View Full Version : syntax of sprintf

05-20-2008, 10:53 PM
until i started using the techniques for avoiding sql injection, i have been using a normal insert and select sql query which worked fine.

i have a registration page where a user enters their username and if this already exists i display a message by executing a select query and if the username does not exist then i run an insert query.

after adopting the technique to avoid sql injection

$username = stripslashes($_POST["username"]);
$email = stripslashes($_POST["email"]);

$username = $_POST["username"];
$email = $_POST["email"];
previously my select and insert query were

INSERT INTO individuals(username, email) values('$username', '$email')
Select username from individuals where username = '$username'

presently the insert query is

$insertquery = sprintf("INSERT INTO individuals (username, email) VALUES ('%s', '%s')",
mysql_real_escape_string($username), mysql_real_escape_string($email));

This insert query is working however the select query is not doing its task as before of checking if the username already exists or not, even if i register with the same username again it does not alert that the username exists.

the select query is

$selectqueryusername = sprintf("Select username from individuals where username='%s'", mysql_real_escape_string($username));

should i change the syntax of the above select query or is there something else in need to do to fix the select query.

also for insert query if i have a numeric value i should be writting %d correct, i have a numeric value however before inserting that numeric value i am appending a character "-" to combine area code and phone number example 09-123 4567 so i am considering this as %s as there is a character. is this correct.

please advice.


05-21-2008, 07:45 AM
I don't see anything wrong with what you have there. I'm assuming that you are actually executing the query, and running logic on the result as well to determine if the result is true.
As for the values, I wouldn't be overly concerned. This isn't C, and more importantly SQL is very forgiving. SQL is nice enough to do a lot of assumptions for you: '1' == 1, and will convert it to fit if it can. You can however typecast your results (I would use a function for creating the sql if this is the case), and use a switch to determine the logic behind it. This kind of defeats the purpose of what you are doing though...
Oh, and another good check - if you are using PHP5+ with mysql 4.1(not sure the number, but sounds right)+, you can use the mysqli object in php, which includes a prepared statement call. This is pretty much identical to what you are trying to do, but also allows batch performance.

I'd give your script a quick run and dump out what the results of your query are, using a while and fetch_array, just to see if you are actually grabbing a result set. Like I said though, you're query looks fine to me.

05-22-2008, 10:36 AM
i have commented the sprintf statement to insert values in the table and used a normal insert statement which i used earlier.

also the select query is now doing its task of checking the username if it is already in the table as i have used

$selectqueryusername = "Select username from individuals where username = '$username'"; INSTEAD OF
$selectqueryusername = "Select username from individuals where username='%s'", mysql_real_escape_string($username); OR
$selectqueryemail = sprintf("Select email from individuals where email='%s'", mysql_real_escape_string($emailID));

the sprintf syntax is =

$conn = mysql_connect($hostname, $user, $passwordidb);

$insertquery = sprintf("INSERT INTO individuals (username, email, ....) VALUES ('%s', '%s',....)", mysql_real_escape_string($username, $conn), mysql_real_escape_string($email, $conn), ....);

the simple insert statement is =
$insertquery = "INSERT INTO individuals(username, email, ...) VALUES ('$username', '$email', ...)";

however what i need is the data should be safe before the insert query is executed and presently the way the sprintf is written is not doing what it is supposed to do. i have taken this idea from the following url


i have tried different combinations of the sprintf statement some dont work and for some all the values are not being inserted into the table.

following are the combinations i have tried.


$insertquery = sprintf("INSERT INTO individuals (username, password....) VALUES ('%s', '%s', ...)", mysql_real_escape_string($username, $conn), mysql_real_escape_string($password, $conn), ...);


$insertquery = sprintf("INSERT INTO individuals (username, password....) VALUES ('%s', '%s', ...)", mysql_real_escape_string($username), mysql_real_escape_string($password), ...);


$insertquery = sprintf("INSERT INTO individuals (`username`, `password`, ...) VALUES ('%s', '%s', ...)", mysql_real_escape_string($username, $conn), mysql_real_escape_string($password, $conn), ...);


$insertquery = sprintf("INSERT INTO individuals ('username', 'password', ...) VALUES ('%s', '%s', ...)", mysql_real_escape_string($username, $conn), mysql_real_escape_string($password, $conn), ...);


$insertquery = sprintf("INSERT INTO individuals (username, password....) VALUES ('$username', '$password', ...)");

in case of 5 prior to the sql insert statement i have used
$username = mysql_real_escape_string($_POST["username"]); ...

i am not sure which is the right method or if there is any other way.

mainly my approach to avoiding the sql injection is

$username = stripslashes($_POST["username"]); ...
$username = $_POST["username"]; ...

$conn = mysql_connect($hostname, $user, $passwordidb);

mysql_select_db($database, $conn);
$insertqueryresult = mysql_query($insertquery);


i would really appreciate if anyone can help me to solve this problem, please suggest the right syntax for sprintf, i have used different combinations in sprintf = " ' ` not sure which is correct.

any help will be greatly appreciated.

waiting for reply.


05-22-2008, 07:57 PM
I still don't see what the problem is - your syntax is correct. This:

$sQry = sprintf("SELECT * FROM Table WHERE username='%s'", mysql_real_escape_string($username));
$rQry = mysql_query($sQry);

Should work without any troubles. What I can tell you is that the word 'password' will need to be referenced as a string - password is reserved in SQL. Thats why I tend to write all my queries with backticks and strings where required:
SELECT * FROM `myTable` WHERE `myField` = 'MyVal'
For example. Other common ones that will trap people are 'number' and 'date'. This is probably why some of your numbered examples did not work, it is attempting to run the actual password function instead of identifying the password column.

Try turning on your error reporting, very first line in your php:
This will tell you if you have any syntactical or logic errors that could hinder the program execution. Try that, see if it gives you any errors.