Including a variable in an php sql query

Associate
Joined
6 Jul 2003
Posts
2,075
Just a quickie guys, I'm making a login script (only college level) where I've got a page to process the username and password submitted using POST. I'm trying to set up an sql query that looks something like below (very roughly). Is it right to include the variables in the query like I've done? I've tried w3schools but can't seem to find my scenario.


$user = $_POST['user'];
$pass = $_POST['pass'];
$query = "select * from users where user=$user and pass=$pass";


It would then go on to ask if any rows were returned (thus proving the username and password matched) to set a session for the username and redirect to the 'success' page, or else give an error that the username and password were wrong. Does that sound about right?
 
Make sure those variables are cleaned up before doing what you're doing otherwise you're going to suffer from SQL injection attacks with code like that!
 
Can you point me somewhere that'll explain that in more depth? This is for a basic college project so I haven't anticipated that. But it'll be good practice for the future no doubt.

From what I gather if someone submits the username as some more SQL query then it'll mess up the results?
 
From what I gather if someone submits the username as some more SQL query then it'll mess up the results?

Basically, yeah. Without input sanitization an attacker can engineer the query to do what he wants. For a more in depth answer, look at this:

http://php.robm.me.uk/#toc-SQLInjection

Short answer: use PHP's mysql_real_escape_string function on any user input that's going to be used in a query.

Other than the injection vulnerability, though, the code looks fine to me.
 
Last edited:
What you will need to do is make sure that no user have put special characters or thinks like quotes in the username/password fields.

Doing this to unsecure code could allow them to inject code into SQL query and for example get access to an account on your system.

Simplest way to avoid this is to "clean" the username, password fields of special characters before putting it into the SQL query.

Try this

PHP:
$user = $_POST['user'];
$pass = $_POST['pass'];

$query = sprintf("SELECT * FROM users WHERE user='%s' AND pass='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($pass));

Change your code to the above, this escapes quotes in your username/password fields (makes em safe), this will stop people injecting code into your query and possible hijacking users accounts.
 
Thanks mossy, just what I needed. Another question as I've seen this before, but what do the %s mean/do?


Ps. would this work:

PHP:
$user = mysql_real_escape_string($_POST['user']);

$pass = mysql_real_escape_string($_POST['pass']);
 
Last edited:
The %s is the variable, so when it comes to sprintf the first option used is the string you want to use.

eg sprintf("my name is %s",....

every option after the string corresponds to the data you want to add to the string.

$myname = "bob";
eg sprintf("my name is %s", $myname);

As you have 2 variables you would use 2 %s symbols and add 2 parts of data to the sprintf command.

$variable1 = "hello";
$variable2 = "world";

sprintf("%s %s", $variable1, $variable2);

The above would output

hello world.
 
me again! instead of making a new thread I thought id just update this one.

making some good progress and learnt a lot but hit a snag. why doesn't this work?

PHP:
$message = "Hi ".$user.", This message confirms your order! Please check below for the items you ordered. \n\n". do {

blah blah stuff here

} while ($row_basket = mysql_fetch_assoc($basket));

The error is about the do being wrong I think, but I don't know how to make it right!

Parse error: syntax error, unexpected T_DO in /home/jordaann/public_html/oaklandia/store/checkoutconfirm.php on line 38
 
Last edited:
Are you trying to concatenate the loop to the string? If so, you can't do that because the loop is a control structure and has no return value.

Also, you need to use a normal while loop rather than a do loop, because you need to get the row before each iteration, rather than afterwards.

If you want to append the row information to the string, do this:

PHP:
$message = "Hi $user, This message confirms your order! Please check below for the items you ordered. \n\n";

while ($row_basket = mysql_fetch_assoc($basket))
{
	$message .= $row_basket['title'] . "\n";
	$message .= $row_basket['shdesc'] . "\n";
	$message .= $row_basket['price'] . "\n";
}
 
Back
Top Bottom