TalkPHP - Securing your MySQL Queries with Sprintf

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Securing your MySQL Queries with Sprintf (http://www.talkphp.com/general/1062-securing-your-mysql-queries-sprintf.html)

Securing your MySQL Queries with Sprintf

Sprintf in itself will not secure a MySQL query from head to toe. That should be made clear from the word go. There are many more techniques that go into ensuring a MySQL statement is safe to execute on the MySQL server. Sprintf will, however, take a lot of the sting out of any malice.

Not only will your new found knowledge help you when it comes to security, but sprintf and its twin brother with a slight genetic mismatch, printf, are on hand to make your PHP code look a lot more programmer-friendly.

Gaping Security Hole

Take the following snippet of PHP code. Although I have hard-coded the $iColumnId variable, assume that it is expecting an integer value, in our case 5, from a user form.

PHP Code:


		
			
$iColumnId = '5 OR id != 0';

$szSQL = "  DELETE FROM
                myTable
            WHERE
                id = " . $iColumnId;

As we were allowed to specify more than the ID, we have effectively crafted ourselves a harmful query. The very reason this is allowed to happen is because the variable is simply concatenated (joined) onto the query and assumed safe. Our query now reads as the following bad, bad query:

Quote:

DELETE FROM myTable WHERE id = 5 OR id != 0

In pseudo-terms this reads delete everything from myTable where the id column equals 5 or doesn't equal 0. What does this mean? Well, it means that the individual who was able to append data to our query has essentially deleted every single record from our entire table. Don't scream!

The Solution

Enter sprintf. Sprintf could have saved us from the nasty surprise of awakening to find an empty table. Sprintf could have been your friend! Albeit sprintf does not have any magical reverse procedure, nor does it even care that your entire table is no more. Sprintf would have cared though if you'd had paid a little more attention to it before placing the insecure file live. Take the following example again but this time we're using sprintf:

PHP Code:


		
			
$iColumnId = '5 OR id != 0';

$szSQL = sprintf("  DELETE FROM
                        myTable
                    WHERE
                        id = %d",
                    $iColumnId);

There we have it! Sprintf has just saved your life, your table, your dignity as well as your credibility. I'm sure those 4 points aren't mentioned in the function's documentation but they are nonetheless byproducts of such simplicity!

As we are expecting an ID we have specified to sprintf that the argument should be an integer and nothing more. This is very much the same as typecasting which is a topic we'll get into in more depth in a couple of days' time. Notice the %d, sprintf has many of these to specify the type of data you are expecting. In our example sprintf has cleverly noticed we wanted an integer and converted our malicious string into an integer. Thus leaving us with:

Quote:

DELETE FROM myTable WHERE id = 5

It has withered the string down to make it an integer. As our string began with an integer, it was able to remove all the string data. Ingenious!

Incidentally: Although a user would be able to specify any integer in this instance, please note that if your table consists of data for many users then you will want to add another WHERE clause to ensure they are unable to delete other users' data. The most common being the following: ...AND member_id = %d

Inner Workings

Sprintf is a nice function in itself, but to any individual who has never approached its live-saving qualities before may find themselves undeniably perplexed. Sprintf works like so:

You specify the first argument which is the string you want to use. In our case this is a MySQL statement but it could be just about anything.
You enter the %d and %s etc. where you want the variables from the arguments to appear based on your desired variable types.
PHP, upon execution, reads the statement. Every time it finds a %x where x could be anything it uses the respective argument (the first %x corresponds to the 2nd argument, the second %s corresponds to the 3rd argument - and so on.

To elucidate this somewhat, if I had the following:

PHP Code:


		
			
$szCats = 'cats';
$szDogs = 'dogs';
printf("Raining %s and %s.", $szCats, $szDogs);

This will leave us with the following string:

Quote:

Raining cats and dogs.

But if I reversed the variables as they appear in the 2nd and 3rd arguments of our printf function (note: printf is exactly like sprintf except the function's output is echoed and not returned) to be like so:

PHP Code:


		
			
$szCats = 'cats';
$szDogs = 'dogs';
printf("Raining %s and %s.", $szDogs, $szCats);

It would leave me with the nonsensical idiom:

Quote:

Raining dogs and cats.

Type Specifiers

Type specifiers are our odd looking %x (where x is anything). These specify the types of data we desire in that position in our 1st argument. In the earlier example we wanted only integers so we specified %d, but in our cats and dogs example we wanted 2 strings so we set them both as %s.

Sprintf and its brother printf support the following type specifiers (taken straight from php.net):

% - a literal percent character. No argument is required.
b - the argument is treated as an integer, and presented as a binary number.
c - the argument is treated as an integer, and presented as the character with that ASCII value.
d - the argument is treated as an integer, and presented as a (signed) decimal number.
e - the argument is treated as scientific notation (e.g. 1.2e+2). The precision specifier stands for the number of digits after the decimal point since PHP 5.2.1. In earlier versions, it was taken as number of significant digits (one less).
u - the argument is treated as an integer, and presented as an unsigned decimal number.
f - the argument is treated as a float, and presented as a floating-point number (locale aware).
F - the argument is treated as a float, and presented as a floating-point number (non-locale aware). Available since PHP 4.3.10 and PHP 5.0.3.
o - the argument is treated as an integer, and presented as an octal number.
s - the argument is treated as and presented as a string.
x - the argument is treated as an integer and presented as a hexadecimal number (with lowercase letters).
X - the argument is treated as an integer and presented as a hexadecimal number (with uppercase letters).

As you can clearly see from the above enumeration, sprintf and printf support a legion of type specifiers. These are all there to save you from the otherwise impending doom of insecure scripts that are susceptible to the notorious SQL injection attacks. They should really be used on a common basis! Somebody breaching your PHP script's security has to be a rather embarrassing situation to be in.

Last but certainly not least, sprintf supports functions inside functions, so to make your MySQL statement even more secure, you could do the following:

PHP Code:


		
			
$szSQL = sprintf("  DELETE FROM
                        myTable
                    WHERE
                        id = %s",
                    mysql_real_escape_string($iColumnId));

Which will escape our string at the same time as ensuring it is a string. This prevents any harmful characters being taken literally.

All in all and security aside, I'm sure the majority of you also agree that using the sprintf and printf functions make your PHP look a lot prettier. Without it I'm confident every PHP script would look similar to Britney Spears (with a bald head or a wig, both are equally as unsightly, right?)

Nice post Adam, I'm sure it'll be a great help.

Doesn't hurt to put single quotes around your escaped string either, even if you're inserting numbers. If you don't run it through sprintf, someone trying to cheat would just end up with a broken query since "id='5 OR id != 0'" doesn't exist.

Most DB abstraction classes have a quote() method to do that for you, if you happen to be using one.

I've never actually seen much advantage in using an external DB class to achieve a similar result. I must say I trust my own code but don't trust other peoples' code very much. If I were to, however, which would you recommend. And why?

Another great tutorial by PHP Lord.

That may be needed for older versions of mysql, but as far as I know new versions require a query to be like this (I know my version of SQL requires it)
SELECT * FROM `table` WHERE `id` = 'value'
not
SELECT * FROM table WHERE id = value

The first way prevents sql injection attacks so long as you clean the incoming data, ` and ' are escaped, leaving you with a safe query.

As far as I'm aware, the acutes simply allow you to have white space in your names, i.e:

select * from `my table`

not

select * from my table

And allows you to use keywords as column headers as well.

Either way, using ` is an easier way to secure your queries.

1. You don't need to use sprintf to secure your queries. Just make sure all user submitted data is the correct data type and strings are escaped (mysqli_real_escape_string()). IMHO using sprintf makes your code harder to read and is more of a pain than typecasting.

2. Integers are not strings. If in your database you have an integer column type you shouldn't insert a string into that column. It just doesn't make sense. In other words, don't put quotes around integers in your query. I know MySQL allows it but it's not correct.

3. ` is only allowed in MySQL IIRC. It's not needed and doesn't make anything more secure. All it does is enclose table and column names.

Quote:

id have to agree there, but i do like the idea, i like seeing new ideas on solving problems, but sprintf isnt for me either, infact i mostly use prepared statements in mysqli these days.

but what ever lights your candle i suppose :)

The concatenation way looks a lot nastier to be fair, in my opinion. And with sprintf() you can typecast the code whilst you're at it. The only downside that I see to it is that it's not the fastest function ever.

i must say, the more i look at sprintf it grows on me, but i suppose from my point of view its a case of 'Better the devil you know' :)

I've been trying to figure out how it works, how to understand and how to implement it into my scripts so I can also 'read' it, like normal plain text. Tho this is not the case.

Can somebody somewhat clarefy this for me, since this is the first time with sprintf en printf for me. :)

Thanks a bunch.
Dutch dude.

Hmm. From a reading it like normal text perspective, that could be a tough one. How I read it is if you have sprintf string like this:

It's raining %s and %s.

All I see is that the text is expecting 2 strings, not 2 integers, or floats, or anything like that. If you wanted to read it in a specific manor then you would have to glance to the 2nd and 3rd arguments of the function and them move back - much like you would if you had foot-notes in essays. So you'd hopefully see 2 descriptive variable names which describe exactly what's going in there:

$szCats, $szDogs.

Implementing it into your code is rather easy, and is required, in some cases, such as the CRC32 function may return an unsigned checksum, you're supposed to use it like so:

php Code:

printf("%u\n", crc32("It's raining cats and dogs."));

Where it could be as simple as:

php Code:

printf("It's raining %s and %s.", $szCats, $szDogs);

If you wanted it to be. It's entirely up to you where you use it, I just happen to use it when constructing MySQL statements.

Hi Wildhoney,

I'm truly impressed about this tutorial! Now I don't feel lonely anymore being a "paranoid" developer. I've been using (s)printf and escaping strings since forever along with a few validation methods I posted already in another thread.

When people implement this kind of code, the internet can become a safer place.

DragonBe

Oh man this is, to be honest, quite hard. I am trying to fix myself up with a nice login system but I can't get the right security I want in place. This might help, although I don't have a good grasp of the code.

Is there a more in-depth explanation?

Thanks anyways guys.

php.net is the place to look respawn.

It's making my head hurt slightly :P But thanks for the tips and the general post WildHoney. I'm still quite some way from understanding and utilising it in my web application(s) but i am learning, and these informative threads (the additional input from the community) are definately helpful :)

Thanks again TalkPHP!