syntax of sprintf

sudhakararaog

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

if(get_magic_quotes_gpc())
{
$username = stripslashes($POST["username"]);
$email = stripslashes($POST["email"]);
}

else
{
$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.

thanks.

leatherback

ehm.. Why make t so complex?

if(get_magic_quotes_gpc())
  {
  $username = mysql_real_escape_string(stripslashes($_POST["username"]));
  $email        = mysql_real_escape_string(stripslashes($_POST["email"]));
  }
else
  {
  $username = mysql_real_escape_string($_POST["username"]);
  $email        = mysql_real_escape_string($_POST["email"]);
  }

and run your query as normal should do the trick, afaik..

laserlight

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.

Declare the username column as UNIQUE so that it cannot contain duplicates to begin with. That said, the SQL statement looks correct, though I would SELECT COUNT(*) instead.

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.

You could have a format string like: "%d-%s", with the entire thing enclosed in single quotes if it is a string in SQL.

Why make t so complex?

It is not complex and has the advantage of keeping the variable ready to be printed instead of merely ready to be embedded into the SQL statement. It also ensures that the SQL statement contains escaped values since one cannot remove the escaping code by accident without removing the SQL statement string entirely.

sudhakararaog

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

http://in2.php.net/mysql_real_escape_string

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

if(get_magic_quotes_gpc())
{
$username = stripslashes($POST["username"]); ...
}
else
{
$username = $POST["username"]; ...
}

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

if(!$conn)
{
}
else
{
mysql_select_db($database, $conn);
$insertqueryresult = mysql_query($insertquery);

mysql_close($conn);
}

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

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.

thanks.

NogDog

You really need to discover the joys of the [noparse]

...

[/noparse] bbcode tags if you want us to expend the effort to read your code.