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

[TroyTime]

i've never bothered with sprintf in that situation

i use addslashes for everything in a query

then i use stripslashes for everything that comes out of a query to display

[tomdkat]

Originally Posted by sudhakararaog

the select query is

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

What does this sprintf() call return?

Peace...

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

1.

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

2.

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

3.

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

4.

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

5.

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

[tomdkat]

I don't think you understood my question. Above, you indicated this isn't working the way you want it to work:

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

That call to sprintf() returns something but you never told us what you're getting back. So, could you post what $selectqueryusername is after running the above code? That way, we can see how the select query is being formatted and get an idea of what is _actually_ happening.

In fact, if you could post what $insertquery is in each of the 5 examples you post above, that would be great. If you simply echo $insertquery after each call to sprintf() and post the results here, we can see what's going on.

Peace...

[Big-K]

I'm not understanding the need for sprintf in the first place.

$insertqueryresult = mysql_query("SELECT username FROM individuals WHERE username = '".mysql_real_escape_string($username)."'");

If this were a long query being used multiple times I could see the sense in using sprintf, but in this case it seems a little...overzealous