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.

How to stop SQL Injection

Discussion in 'PHP' started by adamjblakey, Aug 21, 2007.

  1. #1
    Hi,

    I was just wondering what everyone does to prevent SQL injections and what are the best ways to secure your code.

    As i am wanting to learn how everyone goes about doing this and learn the best methods to protect my code.

    Cheers,
    Adam
     
    adamjblakey, Aug 21, 2007 IP
  2. nico_swd

    nico_swd Prominent Member

    Messages:
    4,153
    Likes Received:
    344
    Best Answers:
    18
    Trophy Points:
    375
    #2
    For numeric/integer values:
    
    $foo = intval($foo);
    
    PHP:
    www.php.net/intval

    And for any other data:
    
    $foo = mysql_real_escape_string($foo);
    
    PHP:
    www.php.net/mysql_real_escape_string

    Do never ever trust the user or think "Nah, they won't do or try this". You can also use preg_* functions to filter the input for what it needs to be.
    For example if you only want to allow characters from a-z you can do:
    
    $foo = preg_replace('/[^a-z]/i', null, $foo);
    
    PHP:
    www.php.net/preg_replace

    Or if you have a select for example, and you want to make sure the submitted value exists in the select, do something like:
    
    $allowed_values = array('foo', 'bar', 'etc');
    
    if (!in_array($_POST['select'], $allowed_values))
    {
        // Handle error
    }
    
    PHP:
    I repeat, don't trust anyone. Think about every variable in your query string and make sure the user has no chance to insert something he isn't supposed to.
     
    nico_swd, Aug 21, 2007 IP
  3. FFMG

    FFMG Well-Known Member

    Messages:
    1,091
    Likes Received:
    39
    Best Answers:
    0
    Trophy Points:
    160
    #3
    A good habit is to wrap all your SQL variables inside your own function without any quotes.

    
    $sql = "SELECT * FROM table WHERE something=".safe($_POST['value']);
    
    Code (markup):
    then your safe(...) function would take care of all the quotes, cleanup, warnings, (or whatever), needs to be done.

    Look at the quote_smart(...) function on php.net, http://www.php.net/mysql_real_escape_string

    But you will need to improve it a little to be safer, (read some comments).

    And be sure to check for invalid requests from the users, ($_GET/$_POST/$_COOKIES etc). You should have one common script to always validate your requests.
    As mentioned, never, ever trust anything/user, (especially the registered users).

    Even inside your own internal functions that you think will never be used with outside variables, always enclose your SQL code inside a safe function.

    And if you are paranoid, (like me), use your own function mysql_query(...) and get it to email you when there is an error, (because there should not be any errors anyway).
    Most hackers will do a trial an error before fully injecting your code, so those initial errors would be reported by your safe function and hopefully allow you to react before it is too late.

    Did I mention, never trust anybody! Open Source, reputable, code does not always mean safe.

    FFMG
     
    FFMG, Aug 21, 2007 IP
  4. Webmoney-Vl

    Webmoney-Vl Peon

    Messages:
    35
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Here the small article:

    All anymore and more coder start making in the scripts co-operating with the bases of information. As a rule, MYSQL is utillized (rarer MSSQL). In this article I will tell about the measures of defence of Php+mysql, as with MSSQL I do not work and it gives where more possibilities are for a hacker, what MYSQL. Essence of uyazvimostey of such type consists in that through variables which are passed through a parameter to the script and participate in a SQL, a malefactor can inculcate the SQL-code and the same to modify a query to DB. I not will in detail sharpen attention on principle of SQL-inj, but will simply tell how to avoid them. Those, who wants to know, as there are SQL-inj, can read about them here. I will make a not large example:
    
    SELECT * FROM table WHERE user='$go'
    
    Assume this variable $go passed as a parameter to the script and Sql-comands is executed. All would good, that a hacker can as a value to to to pass user=lamer' (you will pay a regard to quotation mark) a change and to close a condition, and further to inculcate through f-ciyu UNION (this f-ciya appeared with Mysql4, and in more early versions of inj will not turn out) other command SELECT and the same to get confidential information from a base (for example, passwords) if knows its structure. If after a condition would go yet what both other terms (not necessary a hacker) or he would be able easily to cut them so "-" or so "/*" (these characters in MYSQL mean a further comment). By the way, in MSSQL of malefactors would put character ";" (meaning end of command) and to inculcate new (for example, INSERT, UPDATE etc.).
    
    For the decision of problem with SQL-inj we must filter transferrable in Sql-kommande variables. It is arrived at the followings methods:
    1) Never forget to put a value in quotation marks (it will be useful for us for further filtration of variables). I.e. so user='$go', but not so user=$go ! By the way, if a variable is a number, its value also can be specified in quotation marks.
    2) If a variable is a number, securing it is possible as easy as anything, it is necessary simply facilities of PHP to bring it over to the numerical type:
    $go=(int)$go; or $go=intval($go);
    3) If a variable is not a number, we need simply to watch absence of quotation marks, it can be obtained f-ciey mysql_escape_string() (its analogue is addslashes()). F-ciya is intended in an order to put slesh before a quotation mark (\'). Now even if a hacker will make an attempt inculcate the SQL-код, he will not exceed a supply quotation marks.
    
    If you are paranoikom, can do filtration on basic f-cii in MYSQL, which are utillized for injections: UNION and CHAR.
    
    Code (markup):
     
    Webmoney-Vl, Aug 21, 2007 IP
  5. xdimension

    xdimension Peon

    Messages:
    180
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #5
    nico_swd's post is very good summary, your code should be safe if you follow that.
     
    xdimension, Aug 21, 2007 IP
  6. ssanders82

    ssanders82 Peon

    Messages:
    77
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #6
    FWIW, make sure to turn off PHP's magic quotes before you escape your strings or you could be double-escaping. But do turn if off, it sucks.
     
    ssanders82, Aug 21, 2007 IP
  7. adamjblakey

    adamjblakey Active Member

    Messages:
    1,121
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    80
    #7
    Cheers guys... good to hear everyones input and give me some good ideas to include.

    I like the idea to create one big function to apply to all my $_POST

    What would you reckon would be the ultimate function to use to make sure everything is checked?
     
    adamjblakey, Aug 22, 2007 IP
  8. FFMG

    FFMG Well-Known Member

    Messages:
    1,091
    Likes Received:
    39
    Best Answers:
    0
    Trophy Points:
    160
    #8
    It depends, what do you mean by checked?
    all the function would do is ensure that the string are properly formated, (the quotes are escaped).

    But surely you don't only use the $_POST/$_GET for SQL?

    FFMG
     
    FFMG, Aug 22, 2007 IP
  9. adamjblakey

    adamjblakey Active Member

    Messages:
    1,121
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    80
    #9
    I mean a function to strip tags, stripslashes, trim, check if numeric or not add intval or mysql_real_escape_string depending on what the string is. Basically do everything needed.
     
    adamjblakey, Aug 22, 2007 IP
  10. zEron

    zEron Peon

    Messages:
    30
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    this is great advice but how do you use within a vBulletin
    hOW do you use this sir - nico_swd
    /showthread.php?p=11718096#post11718096

     
    zEron, Jun 18, 2009 IP
  11. zEron

    zEron Peon

    Messages:
    30
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    sorry for the double post .... i will try to delete



    This is what I was looking for .... but how do you use it ????


    In vBullitin how is it applied?

    Example for instruction :

    In Admincp
    Styles and templates
    Search and Replace what????


    ........ please help a guide?
     
    zEron, Jun 18, 2009 IP
  12. Joe Porter

    Joe Porter Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #12
    Escaping strings does add some protection, but even if strings are escaped you can still be vulnerable...using parameterized queries is important. Good explanation here that's more in depth than I can go into on a forum:

    http://www.programmerinterview.com/index.php/database-sql/sql-injection-prevention/
     
    Last edited by a moderator: Feb 7, 2013
    Joe Porter, Feb 7, 2013 IP
  13. FFMG

    FFMG Well-Known Member

    Messages:
    1,091
    Likes Received:
    39
    Best Answers:
    0
    Trophy Points:
    160
    #13
    This is not true, escaping strings will work fine.
    Not escaping strings properly, that's the problem.

    Any example you might have will only highlight badly escaped strings.

    FFMG
     
    FFMG, Feb 9, 2013 IP
  14. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #14
    Uhm... wow, people STILL advocating bad techniques and broken methodologies -- completely ignoring the GIANT RED WARNING BOX ON EVERY MYSQL_ FUNCTION PAGE saying... don't use mysql_ anymore.

    ... and really that's the PROPER answer to this question -- do NOT use mysql_ that way you aren't blindly adding strings together which is how you end up shtupped in the first place! I mean I know people are still supporting old/outdated code... but really is it THAT hard to change out the broken assed insecure idiocy for a nice clean safe object implementation?

    I haven't done an escape manually in half a decade, because I did what they said to do -- use prepared queries using PDO or mysqli.

    // assumes $db is a PDO object that is connected to the database
    $statement=$db->prepare('
    	SELECT * FROM table
    	WHERE something = :something
    ');
    $statement->execute(array(
    	':something' => $_POST['value']
    ));
    foreach ($row = $statement->fetch()) {
    	// process it here
    }
    Code (markup):
    100% injection-proof... it's not rocket science. Leave the garbage mysql_ functions and nonsense like manually sanitizing values where it belongs -- in the previous decade. JUST AS PHP.NET HAS BEEN TELLING YOU SINCE PHP 5 DROPPED.

    Herpafreakingderp people...

    -- edit --

    Ok, maybe a little less froth on that, didn't notice this was a bump of a six year old thread. Of course with the effectively invisible light grey on white text for dates, placement of dates in the thread where I wouldn't actually be looking for dates, this is hardly a surprise... something ELSE to override with user.css I guess.
     
    Last edited: Feb 9, 2013
    deathshadow, Feb 9, 2013 IP
    Snat likes this.
  15. FFMG

    FFMG Well-Known Member

    Messages:
    1,091
    Likes Received:
    39
    Best Answers:
    0
    Trophy Points:
    160
    #15
    Yes, I made the same mistake, I also replied to a 6 year old thread...

    FFMG
     
    FFMG, Feb 9, 2013 IP
  16. denndeal

    denndeal Greenhorn

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #16
    hello everyone..how do i code PDO for this script...please help me


    <?php



    if (isset($_POST['Login'])){

    $UserName=$_POST['UserName'];
    $Password=$_POST['Password'];

    $hostname = 'localhost';
    $username = 'root';
    $password = '';
    $database_name = 'ovs';
    $mysqli = new mysqli($hostname, $username,$password, $database_name);

    $login_query=mysqli_query($mysqli,"select * from voters where Username='$UserName' and Password='$Password' and Status='Unvoted' and Year='1st year'") or die(mysqli_error());

    $login_query1=mysqli_query($mysqli,"select * from voters where Username='$UserName' and Password='$Password' and Status='Voted'");


    $count=mysqli_num_rows($login_query);
    $count1=mysqli_num_rows($login_query1);


    $row=mysqli_fetch_assoc($login_query);
    $id=$row['VoterID'];
    ?>
     
    denndeal, Mar 19, 2014 IP
  17. FFMG

    FFMG Well-Known Member

    Messages:
    1,091
    Likes Received:
    39
    Best Answers:
    0
    Trophy Points:
    160
    #17
    You should start your own thread rather than replying to a 7 years old thread.

    FFMG
     
    FFMG, Mar 19, 2014 IP
  18. denndeal

    denndeal Greenhorn

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #18
    ok i will thanks
     
    denndeal, Mar 20, 2014 IP