Preventing SQL Injection

Many web developers ignore the threat of SQL Injection, or are oblivious to it. It is one of the most serious security threats and can result in a corrupted, amended, viewing of sensitive information or even a deleted database. Thankfully, it is fairly easy to protect websites from SQL injection attacks.

What is a SQL injection attack?

Most modern websites are dynamic and source a lot of the content from databases. The database query is often made, in part, from a variable that is the result of user interaction. This is normally in the form of a $_GET or $_POST. The attacker can inject valid SQL code into the database query via a $_GET or $_POST variable.

Example of a SQL injection

<p>The user has submitted a simple form and the variables are passed to a results page using the GET method. This includes the ID of the relevant result.   The results page includes the following code:</p>    <div class="code">  $id = $_GET[‘id’]; <br /> $sql = mysql_query(‘SELECT * FROM mytable WHERE id = $id’); <br />  if(!$sql)<br />  {<br />  exit(&#39;Error retrieving from database!&#39; . mysql_error()) ;<br /> }   </div>  <p>A malicious attacker could insert the following into the <em>id</em>variable: </p> <div class="code"> a&#39;;DROP TABLE mytable; </div>  <p>Therefore, dropping your table!  </p>  <h3>The solution</h3>  <p>Add the following function to your functions file. If you do not have a functions file, then create a separate php file and add the function. Make sure you include the function in any page that requires it.   </p><div class="code">  function sql_secure( $value )<br />    {<br />      if( get_magic_quotes_gpc() )     <br />  {<br />    $value = stripslashes( $value );<br />       } //check if the mysql_real_escape_string function exists<br />       if( function_exists( &quot;mysql_real_escape_string&quot; ) )<br />       {<br />   $value = mysql_real_escape_string( $value );<br />       } //for PHP version prior to 4.3.0 use addslashes<br />       else<br />       {<br />       $value = addslashes( $value );<br />       }<br />       return $value;<br />       }<br />   </div> <p>The above function checks to see if <a href=""><em>get_magic_quotes_gpc</em></a> is turned on. The get_magic_quotes_gpc function will automatically escape POST,GET,COOKIE data, but is very unreliable. We therefore reverse its effect my using the <a href=""><em>stripslashes</em></a> function.   </p> <p>The most effective method of protection to date is the <a href=""><em>mysql_real_escape_string</em></a> function. Our function checks if it is available, and is so, it is used to escape special characters in the string for use in the SQL query.</p>  <p><a href=""><em>Addslashes</em></a> was used before <em>mysql_real_escape_string</em> was released with PHP version 4.3.0 and should be used if mysql_real_escape_string is not available. </p>   <h4>Using the function</h4>  <p>Using the original example, we would amend the code so that the user defined variables runs through the <em>sql_secure</em> function before the SQL query.</p>  <div class="code">  $id = $_GET[‘id’];<br />  $safeid = sql_secure($id); <br /> $sql = mysql_query(‘SELECT * FROM mytable WHERE id = $safeid’); <br /> if(!$sql)<br /> { <br /> exit(&#39;Error retrieving from the database!&#39;. mysql_error(); } <br /> </div>   <p>It is a good practice to validate any variable that it going to be used in SQL query in this kind of way. There are many other ways of achieving this, and you are welcome to try. They should all use the <a href=""><em>mysql_real_escape_string</em></a> function.</p>   <p><strong>Disclaimer</strong> Please use the function at your own risk! We offer no guarantee that it will prevent a SQL injection attack or any other security breach.  </p>


Great Blair works great!

New comments for this tutorial have been turned off.