[FAQ] sprintf - no, it's not a phone company

Floydian · February 1, 2008

I thought for a long time that defining a query string that I could store in a variable, that would later be used in a mysql_query() function was a waste of time. It's like, you still have all that ungodly concatenation to do, and escaping and all that. So what's the point?

But then I discovered sprintf()!

What does sprintf() do? It stores a formated string. Sounds pretty simple doesn't it? It is!

Okay, so why should I use this function? Well, to be honest, when I came across this one I thought the same thing as well. Hopefully, I can show you quickly what is so good about it.

Let's examine a sample query without the use of sprintf, and then with it. The first thing you should notice is how much more readable the code becomes with the inclusion of sprintf.


mysql_query("INSERT INTO staffnotelogs VALUES ('', $userid, {$_POST['ID']}, unix_timestamp(), '$old', '{$_POST['staffnotes']}')", $c);

###################

$q_staff_notes = sprintf('INSERT INTO staffnotelogs VALUES ('', %d, %d, unix_timestamp(), %d, "%s")',
   $userid, $_POST['ID'], $old, clean($_POST['staffnotes']));

query($q_staff_notes);

Let's take a look at what I did here. the first thing you may notice, is the use of the clean() function, the query() function. clean() is a database escaping function I use that removes slashes if magic quotes is on, trims whitespace from the string, applies htmlentities() function, and applies mysql_real_escape_string() function.

query() is a function I use simply because it's shorter than mysql_query, and because I pass $c to the mysql_query function inside the query function. Sure, it's a bit slower, but if you have a million queries in your script, you're probably doing something wrong eh?

Now that I've covered some of the things I throw in my code that you wouldn't know unless you know me, let's get back to the sprintf() function.

%d is a place holder for integers, %s is a place holder for strings, %f is a place holder for floats.

Once of the big benefits of doing this, is automatic type casting! Have you have people try to inject code into your db using a field that is supposed to be an integer? Any userid should be an integer, so if you have a search for user by userid script, it should be typecasting that variable as an integer. This is all the db escaping you need to prevent mysql injection from the userid variable!

The same thing applies to floats. Strings however still need a robust db escaping technique applied to them so you'll want to use something a little better than magic quotes (if your site even has it turned on, which I hope ya'll know it's better to code your scripts to not rely on magic quotes!). Using sprintf allows you to apply the mysql_real_escape_string() function without any concatenation!

It takes a little while to get a feel for how much cleaner this method of coding is, but once you do, you'll never go back!

One last thing that applies anytime you store a query string in a variable before you do your query, is that should there be a problem with the query, all you have to do is echo that query string variable. This makes debugging them a snap.

Let's put it all together, and the benefits of sprintf() are:

variable type casting

no concatenation

easy debugging

clean/easy to read code

Just remember, if you have a bigint column, and you use %d for your variable place holder, the largest number you can store is about 2.1 billion. So you'll need to type cast those money columns as %.0f .0 being the number of decimal places you need.

Stats columns typically get a %.4f so that you end up with 00000000.0000

When inserting or updating strings in a database field, you still need quotes around them, so you'll have to do something like sprintf('select userid from users where username = "%s"', $name);

If you had a column name that was a variable, you wouldn't need the quotes.

sprintf('update userstats set %s = %s + %d where stats_userid = %d', 'strength', 'strength', $userid);

Hope ya'll like that! It's like they say, once you've gone black, you never go back! And it's the same with sprintf.

=================================

Here's the clean and query functions for ya.

<?php

// database escape and htmlentities
function clean($string) {
if (get_magic_quotes_gpc()) {
	$string = stripslashes($string);
}
$string = mysql_real_escape_string(htmlentities(trim($string)));
return $string;
}



// mysql query function
function query($query){
$_SESSION['last_query'] = $query;
$result = mysql_query($query);
return $result;
}

// This function is used because it's easier to write query than mysql_query lol, and I do some other things with it that don't necessarily apply in this context.


?>

Akash · May 11, 2008