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..

somedude3488 commented: Use code tags. -1
Atli commented: Great info! +3

Recommended Answers

All 3 Replies

Good stuff. I suggest that you format the code so that readability is increased.

  • 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...

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 :]

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.