1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Protection from SQL Injection

Discussion in 'MySQL' started by kalius, May 9, 2005.

  1. #1
    I couldn't find an SQL injectiong tread in DP so I think a good discussion of this would be helpfull for a lot of people .

    Ok I'm a security consious noob in PHP/MYSQL, I have created a script that passes parameters in the url and other function that inserts the user input on a form in a database. My url parameters are either strings or integers and the form input is all text. From what I have found to protect my script I should do these things:

    1) if magic quotes is on run strip slashes.
    2) if its an integer convert to integer type.
    3) if its a string replace <space>AND<space>, <space>OR<space, and all other sql commands with and empty string ( I think this would be good since my input shouln't contain these ).
    4) Remove special characters not needed like = and ; (any others?)
    5) use the sscape function for mysql.
    6) quote variables in the query string.​

    Is all this a good solution? anything that I should eliminate because is redundant or not needed?

    Any other php, mysql security tips are welcome.
     
    kalius, May 9, 2005 IP
  2. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    0
    #2
    It makes no sense to try to catch all possible SQL combinations - there's just too many. Use regular expressions to validate your input (in some cases you can just use numeric conversions, just as you describe). Search for quotes in the input - most types of input are not supposed to have any. If you expect some, quote them properly (you can use e/preg_replace for this).

    If you have a choice (I don't think MySQL/PHP supports this), use parameter binding. That is, your SQL statement will look kind of like this "insert into some_table values (?, ?, ?)". Then you provide actual values at run time. This is most effective protection agains SQL injection with those client-side drivers that support it.

    As for quoting query strings - don't quote them too early or otherwise you'll end up with strings you can't use (e.g. if you insert slashes and then use string comparison on such string, it may not match what you expect, yet be perfectly valid).

    J.D.
     
    J.D., May 9, 2005 IP
  3. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Instead of removing stuff that shouln't be in the input, only allow stuff that should be. (There is a big difference there). If the input is first and last name, only allow [a-zA-Z' ] (That's a to z in either case, an apostrophe and a space. Then use add slashes before sending it to MySql.

    For int imput, use ctype_digit() to check for 0-9 if you get anything else, trash it. There's also is_numeric() but it allows other stuff like a comma and period etc.

    Simply define what is allowable input and only allow that. That's my opinion. :)
     
    exam, May 9, 2005 IP
  4. kalius

    kalius Peon

    Messages:
    599
    Likes Received:
    27
    Best Answers:
    0
    Trophy Points:
    0
    #4
    One of my inputs is a Big block of text ( a user review), so I was thinking of leaving everything in except dangerous stuff. If the user types SELECT or LIKE its either somebody with the caps lock on or somebody trying something bad.
     
    kalius, May 9, 2005 IP
  5. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #5
    That's one way of looking at it, but I would simply allow a to z and the punctuation marks. Alternatively, a review could potentially contain sample SQL (Just as this post does), so just escape all quotes and send it on in
    
    $properly_escaped_text = mysql_real_escape_string($input);
    $query = "INSERT INTO `table` (`ip`,`review`) VALUES ('$ip','$properly_escaped_text')";
    Code (markup):
    [edit]Also, read this[/edit]
     
    exam, May 9, 2005 IP
  6. kalius

    kalius Peon

    Messages:
    599
    Likes Received:
    27
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Thanks I'll read that after dinner. I have found that everyone have a different opinion about the subject making it hard to get to the right conclusion and solution.
     
    kalius, May 9, 2005 IP
  7. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #7

    Look at code you know works that has a large user base and is proven for methods that are short and to the point that work.
     
    noppid, May 9, 2005 IP
  8. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #8
    That's how I've learned most of what I know :) Great advice
     
    exam, May 10, 2005 IP
  9. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Here's a pretty good read how a SQL injection attack can compromize the entire network.

    http://www.microsoft.com/technet/technetmag/issues/2005/01/AnatomyofaHack/default.aspx

    More info on SQL injection (SPI Dynamics produces a suite of website vulnerability detection tools):

    http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf

    J.D.
     
    J.D., May 10, 2005 IP
  10. shaileshk

    shaileshk Well-Known Member

    Messages:
    455
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    108
    #10
    shaileshk, Jun 19, 2008 IP