[REQ] SQL Injection Prevention

[Woolie]

Hi everyone, I've just started a new site, and have been playing around with dynamically generated URLs, querying the database using WHERE. I know from previous experience that this makes my site vulnerable to SQL injections. The thing is, I'm pretty new to PHP and don't really understand how I can strip the query of quotes and stuff. I will pay up to 200 points to whoever can eliminate the possibility of injections from the code I send you. Obviously I'm not going to post the URL of the page, because thats just suicide.

I'm looking for someone with a good knoledge *cough cough NedreN cough* of PHP, as the site will be getting around 10,000 uniques a month, and yes I do know that for a fact. I don't want to have it screwed up by some bored cracker.

I'm not going to be around for a couple of days so I might not reply to this topic for a while, don't moan at me if I don't ... Thanks in advance

[Bryon]

I don't need the points, but I can give you some pointers, aswell as some "good habits" to make sure your scripts are secure. I don't have time right now, since I'm at school, but when I get home later on I'll type up another post.

[Chris S]

if you coded your own pages add this

example

$page = $_GET[page'];

$page = addslashes($page);

[Woolie]

Righties, I've pasted the very basic backbone of the tutorial display page below. So the user can go filename?tutorial=1.

<?php
$username="blahblahblah";
$password="blahblahblah";
$database="blahblahblah";
$host="localhost";

$ident = $_GET['tutorial']; 

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tutorials WHERE id='$ident'";
$result=mysql_query($query);

mysql_close();

$i=0;
$title=mysql_result($result,$i,"title");
$category=mysql_result($result,$i,"category");
$date=mysql_result($result,$i,"date");
$shortdesc=mysql_result($result,$i,"shortdesc");
$postby=mysql_result($result,$i,"postby");
$tutorial=mysql_result($result,$i,"tutorial");
$level=mysql_result($result,$i,"level");
$avatar=mysql_result($result,$i,"avatar");
?>


<table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
    <td height="14" class="contenttop">Tutorial Information</td>
    </tr>
    <tr>
    <td class="contentmain">
    
<table width="100%" border="0" cellpadding="5" cellspacing="0" class="contentcontent">
    <tr>
    <td> 

<br/>
<strong>Tutorial Title: </strong> <?php echo ("$title"); ?> 
<br/>
<strong>Category: </strong> <?php echo ("$category"); ?>
<br/>
<strong>Date Posted: </strong> <?php echo ("$date"); ?>
<br/>
<strong>Posted By: </strong> <?php echo ("$postby"); ?>
<br/>
<strong>Difficulty Level: </strong> <?php echo ("$level"); ?>
<br/>
<strong>Short Description: </strong> <?php echo ("$shortdesc"); ?>    
<br/>
<strong>Avatar: </strong> <img src="<?php echo ("$avatar"); ?>"/>
<br/>
<br/>
    </td>
    </tr>
</table>
    </td>
    </tr>
</table>
    
&nbsp;

<table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
    <td height="14" class="contenttop"><?php echo ("$title"); ?></td>
    </tr>
    <tr>
    <td class="contentmain">
    
<table width="100%" border="0" cellpadding="5" cellspacing="0" class="contentcontent">
    <tr>
    <td> <?php echo ("$tutorial"); ?>
    
          
    </td>
    </tr>
</table>
    </td>
    </tr>
</table>
    
&nbsp;

300 points for anyone who can copy and paste that script back with the correct safety measures. If there's any possible chance NedreN could confirm that your method works before I send the points.

Thanks again

Woolie

[Bryon]

Ok, so I am home now.

The first thing I want to say is:

NEVER trust user input at all.

You never know what is actually being submitted to you by a visitor, whether from an HTML form/input, a URL, or any other possible way. You should always validate "visitor supplied data", both for any "weird" characters and to overall make sure what is being submitted is in fact what it "should be".

In your script, your have:

...

//First of all:
$ident = $_GET['tutorial']; 

 // Later in the script:
$query="SELECT * FROM tutorials WHERE id='$ident'";

... 

That right there is directly taking the value of $_GET['tutorial'] (filename.php?tutorial=value) and using it in a MySQL database query. This, at times, can be alright, although what if Magic Quotes GPC was disabled?

Well first let me explain what Magic Quotes GPC is. Magic Quotes GPC is a configuration option in PHP that auotmatically "escapes" all data being passed to scripts automatically with slashes ("\"). This allows for data being submitted to your script(s) to be "made safe", somewhat.

An example would be all single quotes, double quotes, back slashes, etc in a string being passed to your script would have slashes placed before them, effectivly escaping anything harmful. (To an extent.. :-) That is with Magic Quotes GPC enabled/turned on in the PHP configuration file. With Magic Quotes GPC disabled/turned off, these slashes are not added, which is the first way to secure your scripts to SQL injection.

A simple way to check if Magic Quotes GPC is enabled or not would be to use the PHP function get_magic_quotes_gpc(), which returns an integer (0 or 1) whether Magic Quotes GPc is enabled or not.

If you don't know whether or not Magic Quotes GPC is enabled or not, you could use this at the very top of all of your PHP pages/scripts.

<?php
if(!get_magic_quotes_gpc()){
   function deepslash($v){
   return (is_array($v)) ? array_map("deepslash", $v) : addslashes($v);
   }
   array_map("deepslash", $_POST);
   array_map("deepslash", $_GET);
   array_map("deepslash", $_COOKIE);
   array_map("deepslash", $_REQUEST);
   array_map("deepslash", $_GLOBALS);
   array_map("deepslash", $_SERVER);
}
?>

If you use that on every page, almost 100% of everything you have will be "SQL Injection proof", since all harmful characters will be escaped automatically.

Another "layer of security" you might want to add would be using the function mysql_real_escape_string, which makes a given string 99.99990029388009% safe to be passed to a MySQL database server.

All you need to do is pass each string you want to submit as a query to a MySQL server through the function:

...

$query = "SELECT * FROM tutorials WHERE id='$ident'";
$query = mysql_real_escape_string($query);
$results = mysql_query($query);

... 

If you pass everything about to be sent as a query with mysql_query() through that function, you have almost eliminated any possible threat of an SQL iInjection attack.

Here is an example right from the PHP function documentation page:

<?php

// Quote variable to make safe
function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

?>

If, with EVERY MySQL query, you pass the query through the function being defined here (Quote_smart()), you will be safe from SQL Injections.

...

$query = "SELECT * FROM tutorials WHERE id='$ident'";
$query = quote_smart($query);
$results = mysql_query($query);

... 

Well, I hope I have taught you at least something. Any questions you may have just ask me. I'm not going to secure your code for you, I'm going to hope you learned how from reading this, and will be able to do it yourself.

Adios,
-Nedren

[Woolie]

Holy crap NedreN!! Thats like a fully fledged tutorial!

Many many many thanks, thats a lot better than just doing it for me. Points sent.

I think I understand it pretty well now, you explain things very well. Thanks a lot.

Woolie

[Bryon]

Haha, your welcome. :-)

I hope others learn from this aswell..