mysql - What do I need to escape when sending a query?


Translate

When you execute a SQL query, you have to clean your strings or users can execute malicious SQL on your website.

I usually just have a function escape_string(blah), which:

  • Replaces escapes (\) with double escapes (\\).
  • Replaces single quotes (') with an escaped single quote (\').

Is this adequate? Is there a hole in my code? Is there a library which can do this quickly and reliably for me?

I'd like to see graceful solutions in Perl, Java, and PHP.


Toutes les réponses
  • Translate

    For maximum security, performance, and correctness use prepared statements. Here's how to do this with lots of examples in different languages, including PHP:

    https://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks


  • Translate

    I would also escape comments (double dash)

    --
    

  • Translate

    A great thing to use in PHP is the PDO. It takes a lot of the guesswork out of dealing with securing your SQL (and all of your SQL stuff in general). It supports prepared statements, which go a long way towards thwarting SQL Injection Attacks.

    A great primer on PDO is included in the book The PHP Anthology 101 Essential Tips, Tricks & Hacks by Davey Shafik etc. 2nd Ed. Makes learning a breeze and is excellent as a reference. I don't even have to think about anything other than the actual SQL Query anymore.


  • Translate

    Which language are using? It seems like pretty much all of them have built-in SQL escape functions that would be better to use.

    For example, PHP has mysql_real_escape_string and addslashes.


  • Translate

    You're better off using prepared statements with placeholders. Are you using PHP, .NET...either way, prepared statements will provide more security, but I could provide a sample.


  • Translate

    In PHP, I'm using this one and I'll appreciate every comment about it :

    function quote_smart($valeur) 
    { 
        if (get_magic_quotes_gpc()) 
            $valeur = stripslashes($valeur); 
    
        if (!is_numeric($valeur)) 
            $valeur = mysql_real_escape_string($valeur); 
    
        return $valeur; 
    }
    
    
    $IdS = quote_smart($_POST['theID']); 
    $sql = " 
    SELECT * FROM Students 
    WHERE IdStudent={$IdS}; 
    ";
    

    Needs one more verification if a field can be NULL :

    $picture = NULL;
    $theidyouwant = 7;
    $Name = 'WOOD';
    
    
    if(is_null($picture)) 
        $p = 'NULL'; 
    else
        $p = "'".quote_smart($picture)."'"; 
    
    $IdS = quote_smart($theidyouwant);
    
    $requete = "SELECT * FROM Students
        WHERE IdStudent={$IdS} AND
        PictureStudent={$p} AND
        NameStudent='{$Name}';
        ";
    

    That's it enjoy ! (hope the post will correctly send underscores and not &#95 ;)


  • Translate

    Use Prepared/Parameterized queries!


  • King Lee
    Translate

    Use prepared statements.


  • Translate

    In MySQL query, when using LIKE, also make sure to escape the "_" characters as it is not escaped by mysql_real_escape_string.

    For reference, check here


  • Translate

    the MySQL C API has it's own mysql_escape_string(). Using it or it's equivalent would be best.


  • Translate

    I am not sure if MySql supports parameterized queries, if so, you should make an effort to go this route. This will ensure the users input can't do anything malicious.

    Otherwise some "bad" characters in addition to what you mentioned would be semicolon (;) and comments (-- and /* */).