Your code has the variable $username in the top part of your question, but you then have $user in the bottom section.
Are you perhaps meaning to use the same variable?
$username = ($_GET ['user']);
$sth = $dbh->query( "SELECT username, user_state, last_activity, alerts_unread, conversations_unread, message_count
FROM xf_user WHERE username='$user'" );
// ^^ Should this ALSO be $username ?
$row = $sth->fetch();
Edit: Okay, now you are just being cute with your PDO::ATTR_EMULATE_PREPARES. Observe this:
Database and table structure:
Database changed
mysql> show tables
-> ;
+----------------+
| Tables_in_prep |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
mysql> select * from users;
+----+---------+--------+
| id | userid | pass |
+----+---------+--------+
| 1 | Fluffeh | mypass |
+----+---------+--------+
1 row in set (0.00 sec)
And some PHP code that is copied from yours, with the added PDO attribute:
<?php
//$username = ($_GET ['user']);
$username="Fluffeh";
$dbh = new PDO('mysql:host=localhost;dbname=prep', 'prepared', 'example');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$sth = $dbh->query( "SELECT userid, pass FROM users WHERE userid='$username'" );
echo "Trying to use $username.\n";
print_r($sth->fetch());
echo "----------------------------------------\n\n";
?>
<?php
//$username = ($_GET ['user']);
$username="user2693017";
$dbh = new PDO('mysql:host=localhost;dbname=prep', 'prepared', 'example');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$sth = $dbh->query( "SELECT userid, pass FROM users WHERE userid='$username'" );
echo "Trying to use $username.\n";
print_r($sth->fetch());
echo "----------------------------------------\n\n";
?>
<?php
//$username = ($_GET ['user']);
$username="Oh my' or 1=1 or 'm=m";
$dbh = new PDO('mysql:host=localhost;dbname=prep', 'prepared', 'example');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$sth = $dbh->query( "SELECT userid, pass FROM users WHERE userid='$username'" );
echo "Trying to use $username.\n";
print_r($sth->fetch());
echo "----------------------------------------\n\n";
?>
<?php
//$username = ($_GET ['user']);
$username="(select id from users limit 1)";
$dbh = new PDO('mysql:host=localhost;dbname=prep', 'prepared', 'example');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$sth = $dbh->query( "SELECT userid, pass FROM users WHERE id='$username'" );
echo "Trying to use $username.\n";
print_r($sth->fetch());
echo "----------------------------------------\n\n";
?>
<?php
//$username = ($_GET ['user']);
// Changed this one to be a non-string, you might be checking an ID instead.
$username="(select id from users limit 1)";
$dbh = new PDO('mysql:host=localhost;dbname=prep', 'prepared', 'example');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$sth = $dbh->query( "SELECT userid, pass FROM users WHERE id=$username" );
echo "Trying to use $username.\n";
print_r($sth->fetch());
echo "----------------------------------------\n\n";
?>
<?php
//$username = ($_GET ['user']);
$username="bob'; drop table users; \
";
// This one is tricker to do in PHP code. I could easily enter this into a text field however.
$dbh = new PDO('mysql:host=localhost;dbname=prep', 'prepared', 'example');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
//$sth = $dbh->query( "SELECT userid, pass FROM users WHERE id='$username'" );
echo "Trying to use $username.\n";
print_r($sth->fetch());
echo "----------------------------------------\n\n";
?>
And the output:
Trying to use Fluffeh.
stdClass Object
(
[userid] => Fluffeh
[pass] => mypass
)
----------------------------------------
Trying to use user2693017.
----------------------------------------
Trying to use Oh my' or 1=1 or 'm=m.
stdClass Object
(
[userid] => Fluffeh
[pass] => mypass
)
----------------------------------------
Trying to use (select id from users limit 1).
----------------------------------------
Trying to use (select id from users limit 1).
stdClass Object
(
[userid] => Fluffeh
[pass] => mypass
)
----------------------------------------
Trying to use bob'; drop table users; \
.
----------------------------------------
Oh, the reason I left the last one till LAST is this output now in my database:
mysql> show tables;
Empty set (0.00 sec)
Yes, that's right, I just dropped a table. Let me say that again, I had a select statement, and with a little trickery I entered in a value that ANYONE with half a brain and some malicious intent could do into a text field, and DROPPED YOUR TABLE.
Now, granted, if you are setting things up properly, you might well set up a different user for the select statements, and only grant them select rights from your database, to stop this sort of thing happening - but lets be honest... you aren't are you?
Clearly setting that emulation is not enough. Seriously, now PLEASE do go read that answer, use prepared statements and use params if you want to be secure in your code.
