Pinkie, thanks for posting the code.
$sql = 'SELECT a.videote as videote, b.user_name as user_name'.
' FROM '.$video.' as a,'.$users.' as b'.
' where b.user_name=$_GET['user'] and... //if i replace $_GET['user'] with john then it works
There are a few issues here, but we can work through them.
A syntax error is introduced when you change the string. You have the right idea with the way $video and $users. But when adding the $_GET['user'], PHP thinks that the first apostrophe is ending the current string.
Consider that:
' where b.user_name=$_GET['user'] and...'
looks like two strings, separated by the word "user":
' where b.user_name=$_GET[' user '] and...'
This isn't proper syntax, so the 500 error is returned. I'm guessing that your error would go away if you tried:
' where b.user_name=' . $_GET['user'] . ' and...'
The next issue is that if a user were to send a carefully crafted value for the "user" parameter, they could cause the query to behave in a way that you didn't intend.
Try this: create a file called login.php, with the following content:
<?php
// Just display the output; no HTML formatting needed
header("Content-Type: text/plain");
// This must succeed, or mysql_real_escape_string() won't have any effect
mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
OR die(mysql_error());
$safeQuery = 'SELECT count(*) FROM users WHERE user=\'' . mysql_real_escape_string($_GET['username']) . '\' AND pass=\'' . mysql_real_escape_string($_GET['password']) . '\';';
echo " safeQuery is: $safeQuery\n";
$unsafeQuery = 'SELECT count(*) FROM users WHERE user=\'' . $_GET['username'] . '\' AND pass=\'' . $_GET['password'] . '\';';
echo "unsafeQuery is: $unsafeQuery\n";
?>
Load login.php?username=bob&password=sample. The output looks reasonable:
safeQuery is: SELECT count(*) FROM users WHERE user='bob' AND pass='sample';
unsafeQuery is: SELECT count(*) FROM users WHERE user='bob' AND pass='sample';
Now try loading login.php?username=bob&password=sample' OR 'hello'='hello":
safeQuery is: SELECT count(*) FROM users WHERE user='bob' AND pass='sample\' OR \'hello\'=\'hello';
unsafeQuery is: SELECT count(*) FROM users WHERE user='bob' AND pass='sample' OR 'hello'='hello';
The safe query will return zero, unless you have a user called bob whose password really is sample' OR 'hello'='hello".
The unsafe version, however, will return the total count of users in the database. The WHERE clause is now:
WHERE user='bob' AND pass='sample' OR 'hello'='hello'
The OR 'hello'='hello' will make the condition true in all cases, even if bob doesn't exist or has a password other than sample.
Part of your query could even be commented out. Try login.php?username=bob' --:
safeQuery is: SELECT count(*) FROM users WHERE user='bob\' --' AND pass='';
unsafeQuery is: SELECT count(*) FROM users WHERE user='bob' --' AND pass='';
The password parameter is now ignored, because it's embedded within a SQL comment.
So even if you're just executing a SELECT statement, the results can be manipulated by a clever user if their inputs weren't escaped.
You can use mysql_real_escape_string to protect against such bad values. This function will add backslashes where necessary to keep input data from being executed as SQL.
$sql = 'SELECT a.videote as videote, b.user_name as user_name'.
' FROM '.$video.' as a,'.$users.' as b'.
' where b.user_name=\'' . mysql_real_escape_string($_GET['user']) . '\' and...';
Example 1 from the php.net page for mysql_real_escape_string has a great example using sprintf:
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
mysql_real_escape_string($user),
mysql_real_escape_string($password));
Each %s is replaced with the parameters (in the order they are specified). This makes it easier to keep your query readable, while protecting against bad input data.
