Prevent SQL Injection Attacks - MySQL

If you have a website that uses a database, then taking a few precautionary steps can keep you safe from SQL injection attacks.

What is an SQL Injection attack?
Basically, it is when an attacker "injects` additional parameters into a SQL query with the intention of:
1. Returning additional database information such as usernames,passwords,etc...
2. DROPPING,TRUNCATING tables
3. Returning an error so the attacker can understand the database schema better and create more finely tuned queries for injection.

There are other reasons of course, but the examples above should give you a good idea of it`s purpose.

Tips for Preventing SQL Injection Attacks

1. If your query takes in a PRIMARY KEY, or INDEX which is an integer, then make sure you check that the query parameter passed in is an integer.

Example: Lets say that your page: getdata.php?pageId=1
Takes in the pageId URL variable and processes it`s value in a SELECT query.

In PHP:

$pageID = $_GET[`pageId`];

$query = "SELECT * FROM pages WHERE id = $pageID";
....


Now if someone passed into the URL:
getdata.php?pageId=-40 or 1=1

Or something similar, then there could be a chance that all records from the pages table would be returned.

To prevent this, simply detect if the URL variable is an Integer.

if(is_numeric($_GET[`pageId`]))
$pageID = $_GET[`pageId`];
else
//KICK USER OUT OF PAGE, OR TAKE OTHER ACTION HERE


The is_numeric function will make sure that the parameter is a number. You can also use the is_int function, but is_numeric has worked better for me in the past.

The same rules apply when dealing with $_POST vars submitted via a FORM or AJAX call.

2. Another thing you could do is add addSlashes() to your query parameter so if a user attempts to put in " or `, they will be parsed out properly.

So if a query looks like:

$myPage = $_POST[`mypage`];
SELECT * FROM pages WHERE name = `$myPage`;


A user may attempt to write the following in the form field `mypage`:
xoxo` OR 1=1

Which could cause an injection.

However, by using addSlashes, the ` character is parsed out properly:

$myPage = addSlashes($_POST[`mypage`]);
SELECT * FROM pages WHERE name = `$mypage`;


Update: A better method instead of addSlashes would be mysqli_real_escape_string(), please look at the PHP's documentation located at http://www.php.net/manual/en/mysqli.real-escape-string.php which will help prevent SQL injections.

However, the basic tips in this article should serve as an easy way to help prevent SQL Injection attempts. Although these concepts were described in PHP, the idea should be the same in other languages such as ASP and Coldfusion.

Keywords: Prevent SQL Injection Attacks, MYSQL Injection


Submit a Comment






Comments

Cody

Posted on: Oct 28, 2008 10:07 am

Good Advice and nicely put Phil.