I’m going to demonstrate a very short and simple method of avoiding SQL Injection at the SQL query level. You’ll need MySQLi support, on Debian you can apt-get install php5-mysql will contain everything that you need, and would be installed by default with your LAMP Installation.

Calling: http://www.mynonexistentdomain.com/test.php?user_id=10&record_number=1

Ignoring any PHP escaping functions or validation for example, you may wish to take the ‘user_id’ from user input, and prepare a query with it, in it’s simplest and most insecure form: $sql = “SELECT username FROM users WHERE user_id=’” . $_GET['user_id'] . “‘ AND rnum=’” . $_GET['record_number'] . “‘;”; If you’re not yet sure yet why this is insecure, read my SQL Injection article. You do have the option of cleaning up your input with mysql_real_escape_string() but this article briefly demonstrates another approach – using prepared statements. It is important to note that you’ll need to establish a MySQLi connection in order to use this feature.

In OO (Object Oriented) PHP

$db = new mysqli($host,$user,$passwd,$dbname); //Connect to our database
$statement = $mysqli->prepare(”SELECT username FROM users WHERE user_id=? AND rnum=?”); //Prepare a statement, using ‘?s’ for our placeholders
$statement->bind_param(’ii’, $_GET['user_id'],$_GET['record_number']); //Replace our ?s above with our input, specifying ‘ii’ meaning that we are passing two integers.
$statement->execute(); //Run the query

We could even now reassign variables to $_GET and $_GET and re-execute $statement->execute(); as our statement has already been prepared.

In the case above, we are using bound parameters. We can also use bound results which is however not going to be covered in this article as it is not directly involved in SQL Injection prevention. Here’s a good tutorial for further reading: http://forum.codecall.net/php-tutorials/12442-php-5-mysqli-prepared-statements.html

The $statement->bind_param is the instruction to only accept ‘i’ or integer type. You could also use ‘d’, ’s’ and ‘b’ – double, string and blob respectively. This is what filters out the potential exploit. You would still want to be cleaning input though, and checking for errors..

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

Votes + Comments
Great info!
Use code tags.
7 Years
Discussion Span
Last Post by jomanlk
  • Technical part of the post can be OK, I'm not sure as I'm not PHP person
  • OP just did copy and paste and did not bother to use code tags, that is bad an unprofessional
  • OP is too much superstitious that there can be no negative opinion in his vote. Big failure...

Edited by peter_budo: n/a


OP is too much superstitious that there can be no negative opinion in his vote. Big failure...

heh, not spoiled for choice at any rate :]

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.