SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Injections

    Hello,

    I am currently developing a new website and I decided to use classes and functions and stuff. I found a really simple function for running a query (OH WOW...)

    PHP Code:
            // run a simple query function
            
    function query($query)
            {
                
    $query mysql_query($query) or die(mysql_error());
                return 
    $query;
            } 
    I've known about SQL injections for a while now, but I have never really done anything to protect against them. I assume that this "run a simple query" function is pretty vulnerable to SQL injections, so how can I protect against it? I've read quite a few articles and I've seen a lot of different methodologies, but I just want one that... works.
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    something like:
    PHP Code:
    // run a simple query function 
            
    function query($query
            { 
                
    $query mysql_query(mysql_real_escape_string($query)) or die(mysql_error()); 
                return 
    $query
            } 
    Just as you would with a non functioned query.

    Spike
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by spikeZ
    something like:
    PHP Code:
    // run a simple query function 
            
    function query($query
            { 
                
    $query mysql_query(mysql_real_escape_string($query)) or die(mysql_error()); 
                return 
    $query
            } 
    Just as you would with a non functioned query.

    Spike
    Would that work?

    Code:
    SELECT * FROM table WHERE name='somename';
    would be mysql_real_escape_string'd to:

    Code:
    SELECT * FROM table WHERE name=\'somename\';
    which isn't a valid query.

    Or am I missing something here?


    Tipem:
    Your function is fine, but you need to pay attention when building the actual query:

    PHP Code:

    $name 
    $_GET['name'];

    $query "SELECT * FROM table WHERE name='".mysql_real_escape_string($name)."';";

    $result query($query); 
    The mysql_real_escape_string function makes sure no one can enter any bad data into your query. For more information check out examples 1 and 2 on this page: http://www.php.net/mysql_real_escape_string#AEN103915

  4. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    nuts, yup you are right Immerse, my bad!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    Wadge! F4nat1c's Avatar
    Join Date
    Oct 2005
    Location
    South Wales, UK
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's right. You'll need to do the sanitizing outside of the function, and then then sanitized code can be run through the function.
    OMFG SitePoint ROXORZ TEH BIG ONE111!
    Wish you were invisible?

  6. #6
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, clean your variables prior to putting them into the query statement. This is a preference btw not a fact.


    PHP Code:
     
    $data 
    trim($_POST['data']); //First I trim off any spaces around variable
    $data intval($data); //If it needs to be a numeric value then use this
    $data number_format($data,2,'.',''); //I use this if it needs to be in USD and db friendly
     
    $sql "INSERT INTO db (field) VALUES ('$data')"
    Again thats just my preference. Saving lines to me means squat when you cant read it.

    Silly

  7. #7
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So my understanding is so keep my function the same, but use mysql_real_escape_string, like so:

    PHP Code:
    // include the class
    include('./myClass.php');

    $query $myclass->db->query("SELECT * FROM table WHERE name='".mysql_real_escape_string($name)."';");

    // display query results here n' crap 
    Or, is there an easier way?
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  8. #8
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Yup, that'll go a long way to making your code injection-proof!

  9. #9
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was just reading over vBulletin's software and what they use for escaping strings. Their query reads like this:

    PHP Code:
                $attachs $db->query_read("
                    SELECT dateline, thumbnail_dateline, filename, filesize, visible, attachmentid, counter,
                        IF(thumbnail_filesize > 0, 1, 0) AS hasthumbnail, thumbnail_filesize,
                        attachmenttype.thumbnail AS build_thumbnail, attachmenttype.newwindow
                    FROM " 
    TABLE_PREFIX "attachment AS attachment
                    LEFT JOIN " 
    TABLE_PREFIX "attachmenttype AS attachmenttype USING (extension)
                    WHERE posthash = '" 
    $db->escape_string($posthash) . "'
                        AND userid = " 
    $vbulletin->userinfo['userid'] . "
                    ORDER BY attachmentid
                "
    ); 
    Note the $db->escape_string... could I use something similar to that? If so, what would my class/function look like? Something like this?

    PHP Code:
    class myclass
    {
        function 
    escape_string($str)
        {
            
    $str mysql_real_escape_string($str);
            return 
    $str;
        }

    Should this work?
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  10. #10
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Yeah., you can do that too. I do something very similar:

    PHP Code:
    class DatabaseMySQL {

    // snip!

        
    function escape($string) {
            if(
    $this->is_connected) {
                return 
    mysql_real_escape_string($string$this->db_con);
            } else {
                return 
    addslashes($string);
            }
        }

    // snip!


    The only extra thing is that I check to see if I've actually connected to the MySQL server yet, because if there's no connection, mysql_real_escape_string will give a warning. Unless you're connecting 'on-demand', you don't need to worry about that though.

  11. #11
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where do I need to define $this->is_connected and $this->db_con, or do I not need to define it at all?

    Will this work (snippet from my class):

    PHP Code:
    class thedailyneopets 
    {
        class 
    db 
        
    {
            
    // connect to the database
            
    function connect($db_host$db_user$db_pass$db_name)
            {
                
    $db_con mysql_connect($db_host$db_user$db_pass);
                
    mysql_select_db("$db_name") or die(mysql_error());
                
                if (
    $db_con)
                {
                    
    $this->is_connected true;
                }
            }
            
            
    // run a simple query function
            
    function query($query)
            {
                
    $query mysql_query($query) or die(mysql_error());
                return 
    $query;
            }
            
            
    // escape mysql query string
            
    function escape($string)
            {
                if (
    $this->is_connected)
                {
                    return 
    mysql_real_escape_string($string$this->db_con);
                }
                else
                {
                    return 
    addslashes($string); 
                }
            }
        }

    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  12. #12
    Wadge! F4nat1c's Avatar
    Join Date
    Oct 2005
    Location
    South Wales, UK
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look's right to me.
    OMFG SitePoint ROXORZ TEH BIG ONE111!
    Wish you were invisible?

  13. #13
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can anybody else give me some more clarification on this?
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  14. #14
    SitePoint Addict RRWH's Avatar
    Join Date
    May 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Understanding how your code could be exploted allows you to remove the risk.

    If you google for "SQL Injection Protection" you will find a lot of info out.

    I used to use a tutorial that has now dissapeared that was very useful.

    In order to better protect against SQL injection attacks against your Database - first read up and see how they work. Try all the standard, simple techniques against your own database powered apps and see if you can break things or get an unexpected result set returned.

    The appropriate steps to take to minimise the chance of an SQL injection attacks are:

    1. Never trust user input
    2. See Rule 1,
    3. Validate all User input eg: if you are expecting a number - make sure that is what you get
    4. Look for some standard injection signatures such as -- (dash dash) which is the start of an SQL comment
    5 See rule 1.
    6 once all data is in the expected format and sanatized, then go the extra step and go ahead and use mysql_real_escape_string()

    Now, you are finally ready to use the input data to either query or update a database.

    Once you have developed a simple data validation function or class you should read up on all the available techniques of actually doing an injection and making sure your data is safe on a test database. It all really comes down to common sense and knowing thy enemy!

  15. #15
    Evil Genius MilchstrabeStern's Avatar
    Join Date
    Nov 2003
    Location
    Arizona
    Posts
    1,131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Couldn't you write a query_string to build your query string with the help of your escape function. Then you can just take the result from that and run it through your query function? That's how I would aproach, cleans up code a little bit.
    ]

  16. #16
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually if you just look up mysql_escape_string in the php manual it gives you an example of protecting yourself from sql injection. Also read the comments on that page, gives you even more insight on the topic.


    Silly


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •