I'm going to be writing a script that takes input from users and stores the info in a MYSQL database.

What is needed to stop SQL Injections and other hacking techniques?

Is all I need to do escape the string with mysql_real_escape_string()?

Thanks for the advice,
Nathan

Use stored procedures. As long as you invoke them properly in your data access layer, their arguments - which would be the user input - can't be mistaken for code you're asking the server to execute.

http://www.databasejournal.com/featu...le.php/3525581
http://www.databasejournal.com/featu...le.php/3547931

This thread shows exactly what you don't want to do:

http://www.daniweb.com/forums/thread41143.html

if ($result = mysqli_query($link, "call se_proc('crm')")) { 
    /* Fetch the results of the query */ 
    while( $row = mysqli_fetch_array($result) ){ 
        echo ($row[0]. "--------- SR. " . $row[1] . "<br>"); 
    }

I don't know PHP well enough to say how to do this, and I'm guessing you aren't in the mood to switch over to .net because there's advice on this particular question available. That said, if one framework can do it, others should be able to do the same thing.

You should be able to run a network trace and see 1 or 1,234,567 go across the wire as four-byte integers if you send them as a parameter to a proc. That moves the data where it needs to be, while at the same time completely isolating data from code. No matter how clever somebody on the other end is.

Escaping all input might not be enough. In SQL Server, a person can feed in hex codes as 0xFF to construct a string, and then execute the contents of that string. That would require some knowledge of how the database and web app are built, but it's a hole escaping doesn't plug. Escaping and unescaping strings is going to have a performance cost, where stored procedures actually have several performance benefits. They tend to run a little bit more quickly, need less memory to execute, and cache drastically better ... mostly because they're precompiled when you 'store' them instead of each time you issue a request. So, all around, it's a better option.

Well, you've piqued my interest. Can you provide an example where this code can suffer from a SQL injection?

PHP Code:

<?php
function quote_smart($value,$add_quotes=true,$allow_wildcards = false)
{ // Taken from the PHP site, with modifications for quotes and wildcards.
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not a number or a numeric string
   if (!is_numeric($value)) {
     if ($add_quotes){
       $value = "'" . mysqli_real_escape_string($value) . "'";
     } else {
       $value = mysqli_real_escape_string($value);
     }
   }
   if (!$allow_wildcards) {
     $value = str_replace('%','\%',$value);
     $value = str_replace('_','\_',$value);
   }
   return $value;
}

require_once('database.php'); //Assume this provides a mysqli database connection into $link

if (!empty($_POST['search_term'])) {
  if ($result = mysqli_query($link, "SELECT field_name FROM searchable_table WHERE field_value = ".quote_smart($_POST['search_term'])) {
    if (mysqli_num_rows($result) > 0) {
      while ($row = mysqli_fetch_object($result)) {
        echo 'Result: '.$row->field_name.'<br />';
      }
    }

    mysqli_free_result($result);
  }
} else if (!empty($_POST['add_term'])) {
  if ($result = mysqli_query($link, "INSERT INTO searchable_table SET field_name=".quote_smart($_POST['add_term'],true,true))) {
    mysqli_free_result($result);
  } 
}
mysqli_close($link);
?>
<form method="post">
Search Term: <input type="text" name="search_term" />
<input type="submit" value="Fetch Results" />
</form>

<form method="post">
Add Term: <input type="text" name="add_term" />
<input type="submit" value="Add Term" />
</form>

Now, please note that I'm not asking this to learn how to hack others. I use very similar code in my daily work and if it's open to an attack, I need to fix it immediately. Both responses to this post have said to use stored procedures. I highly doubt that every SQL query you use should be a stored procedure (correct me if I'm wrong). Also note that that code was put together fairly quickly from some samples of stuff I normally use, but that I usually do a bit more data sanitization that what is presented, though some fields may have no more security than that.

I think its called binded parameters and not stored procedures which are a totally different thing. Anyway, i don't think using binded parameters are absolutely essential, you can use mysql_real_escape_string as long as you absolutely make sure that each data being passed to database query is first escaped through this function.

http://www.hiveminds.co.uk/node/3104

http://www.webmaster-talk.com/php-fo...injection.html

What's interesting about a lot of tutorials is that they don't account for wildcard characters. For example,

PHP Code:

$username = addslashes($_POST['username'])

$query = mysqli_query($link,"select * from user where username LIKE '".$username."'"); 


poses a risk if the user enters % as their username as it will match everyone.

sql injections ?
just use addslashes for char data, use intval or similar where you expect numbers..
what else ? oh yeah.. escape special chars

And do not use LIKE in your selects, it is the most expensive way to find anything.

I use

$query = "SELECT * FROM table where column LIKE '%$searchtext%' ";

For the search function on my site. What's the alternative?

The alternative, Nathand, is to fix the case of the string (so, in my example, we could store usernames as lowercase in the database and only compare the lowercase version). I don't like that idea as it destroys the casing put in by the user, but another option would be to create another field in the table which was the hash of the lowercased username. For text searching where the terms are entered in by the web visitor and you need it case-insensitive and the text can't be fixed or hashed as described above, then LIKE is the best thing to use. If LIKE was so bad, it wouldn't be part of the language (blanket statements are almost always wrong.)

The real important thing to keep in mind is to optimize the structure of your database. A well-structured database suggests ways of intelligently searching. Let me take your query, for example, If your column was "name" and you had first name and last name, separated by a space and someone was searching for last name then that comparison would be substantially slower than if you had a first name column and a last name column and then searched on the proper column for your search term and you could dump the % which slow down the searching by a lot. You would still use LIKE, though, so that you didn't have to standardize names to be all lowercase in their storage.

Originally Posted by m3gadeth

sql injections ?
just use addslashes for char data, use intval or similar where you expect numbers..
what else ? oh yeah.. escape special chars

Don't use addslashes() to prevent SQL injections unless you have no other alternative! This is one of the biggest myths floating around in PHP/MySQL security. mysql_real_escape_string() is much better, but even it still has its imperfections.

So is it agreed that using "mysql_real_escape_string()" on all query's before submitting them will stop most SQL Injections?

Nathan

Oh, and thank you for the info Jeremy. I'll keep that in mind when structuring tables in the future.

Nathan

Originally Posted by Nathand

So is it agreed that using "mysql_real_escape_string()" on all query's before submitting them will stop most SQL Injections?

Nathan

NO. It "prepends backslashes to the following characters: \x00, \n, \r, \,', " and \x1a" (see http://us3.php.net/mysql_real_escape_string ). If you do a query that permits wildcard characters such as % and _, then they are NOT escaped and you MUST account for that.

It seems that people have ignored my function in the challenge code above which specifically addresses this issue. You do so at your own peril. I cannot harp on the issue enough. I suggest you take the fact that no one has stepped forward amongst all the geniuses on this forum to demonstrate the hackability of my code above. While no proof against something is not proof that no proof exists, it does lend to its credibility.

So, if you don't know what you're doing and want to be more sure than ever that your code is secure please use the quote_smart code above. If you know that that code has an error, then please share.

OK. I'd like to beat a dead horse that keeps rising from the dead:

mysql_real_escape_string AND addslashes DO NOT MAKE YOUR CODE SAFE!!!!!! More steps must be taken. The quote_smart function above was written after I studied this for awhile all over the net and I have yet to have anyone hack it. In fact, I included it an open source project I just started at http://sourceforge.net/projects/web20cart because it seems to be very robust. Please, please, pretty, pretty please do not try to take the apparently easy road on this. You MUST consider each field being inserted/updated/queried and the nature of the query before being able to ensure safety.

Again, I ask anyone out there who can demonstrate a vulnerability in the code above to demonstrate it. I promise to patch it ASAP and post it in this forum so that we all may have the best available secure function out there. This is simply too important an issue to be taken lightly.

Jeremy: So your code just escapes the string in addition to these special characters "%" and "_" ?

Developers often forget procedures that will stop their database from being vulnerable to malicious people. One thing you can do is disable allow_url_fopen in the php.ini file. Oddly, default value to this setting is 1, which allows remote access to your database. http://php.net/filesystem

Originally Posted by Nathand

Jeremy: So your code just escapes the string in addition to these special characters "%" and "_" ?

For escaping, essentially yes. It also adds quotes around the value if it is not numeric. As a function it allows you to decide whether to escape wildcards (i.e. % and _) so that on inserts, you can keep them in there and on selects involving LIKE, you can ensure they're safe. I took that code piece from http://us3.php.net/mysql_real_escape_string and modified it to address their note under the code on how to stop SQL injection attacks

Note: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

So that you get an all-in-one package.

Originally Posted by wayfarer07

Developers often forget procedures that will stop their database from being vulnerable to malicious people. One thing you can do is disable allow_url_fopen in the php.ini file. Oddly, default value to this setting is 1, which allows remote access to your database. http://php.net/filesystem

I don't know that this is the best suggestion. I write scripts all the time which open URLs (to read RSS/ATOM feeds, for example). I'd suggest that if you want to stop MySQL from allowing remote access that you configure MySQL to not allow it instead of looking for a language dependent solution -- couldn't I use perl and bypass the php.ini settings?