make query safe

Posted on 2012-08-20
Last Modified: 2012-08-21
mysql_query("insert into table1(column1) values ('')");

I am getting values from php query string

what code do I need to add  to make query safe so no injection
Question by:rgb192
    LVL 17

    Accepted Solution

    Every field value you enter into the query string should be escaped upon building the insert statement. At the very least, use mysql_real_escape_string, though PHP manuals recommend to use either mysqli or PDO extensions. Also, watch out for the default character set (see above manual entries for instructions).
    LVL 34

    Assisted Solution

    Data sanitation itself is cleaning up the values at the very beginning.

    Make sure that any fields that should be numeric will stay numeric:

    $someIDField = $_GET["someID"]; // Get the value from the query string
    $someIDField = $someIDField + 0; // Adding 0 is a quick way to force PHP to convert it to a pure number (to perform the math) without changing the original value. So any non-numeric digits will be thrown out.

    Determine whether or not you need the ; and " and ' characters in your string values. If not, remove them:

    $strnigValue = $_GET["someString"];
    $stringValue = str_replace(array(";","'",'"'),"",$stringValue);

    Try your best to make sure that field values match up to what you expect them to contain. Most numeric fields shouldn't contain anything but digits and a single decimal points. If you're dealing with accounting, then you might want to include the dash or the ( ) characters, too.

    Data sanitation (whenever possible) should always be your first step. Your next step should be to use mysql_real_escape_string like Garry-G suggested:

    mysql_query("INSERT INTO table (stringColumn) VALUES ('".mysql_real_escape_string($stringValue)."')");
    LVL 13

    Assisted Solution

    LVL 17

    Assisted Solution


    PDO is nice, it is what I use by default for my database access, but if you are stuck using mysql or mysqli to accomplish your goal (not all hosts install PDO) then you should be using this function


    depending on the library you are using.

    you should note that while similar the implementation of procedural style of mysql_real_escape_string and mysqli_real_escape_string are different, though I believe the suggested method is the object oriented method.


    Note also the note in the mysql_real_escape_string function, you should be using mysqli not mysql. The problem is a lot of code is out there for people to cut and paste that is using the old mysql library and it is causing a lot of people to write non secure code. There are even best practices articles out there showing some things that are no longer even suggested practices... so be careful.

    In my opinion prepared statements are your best bet. Even using PDO or MySQLI you can still execute badly formed queries that could include injections or buffer overflows.

    But as noted in posts above, sanitize your data first, make sure what you are pulling from the url makes sense (is in the form you expect), then make sure that you are also using a safe method for storing that data in the database.

    Author Closing Comment


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now