How to correctly reference a PHP session variable in a SQL statement

Question

I have seen a few postings about this online, but none of the recommendations have worked in my case.

My situation is simple.. a user logs into a php form, authenticates with the backend mysql table, starts the session, carries them to the next page.

What I am wanting to do is bring out the rest of that users data into textboxes, tables, whatever. However, the only way I have been introduced to doing this is by referencing the session variable. What I have so far is below and I have tried many variations of it, but nothing seems to work (i.e. no values, page doesn't load, etc). I am open to other options though. Thanks all.

$query = "SELECT * FROM users WHERE username = '{$_SESSION['user']}'"

Before running the query var_dump($_SESSION['user']) to see what's in it. Is it what you expect? — Keeleon, Apr 04 '13 at 16:56
You'll probably have to give a lot more details. What isn't working? Is the `users` table empty? What is the code that inserts the data in the first place, and is *that* working? — Mike Christensen, Apr 04 '13 at 16:57
Possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) Session variables are still variables. — Álvaro González, Apr 04 '13 at 16:57
There shouldn't be a problem with that query. Are you getting errors elsewhere in your app? Perhaps show some code around this so it can bee looked at to make sure it works. Also, if you are going through the trouble of storing the username in session, why not just store all use data and avoid additional DB call (that is assuming the user data is not really large)? — Mike Brant, Apr 04 '13 at 16:58
The users table is not empty, if it was, I wouldn't be able to log in to the form. But I forgot to add the session_start() at the beginning of the script, which I have now added. But the recommendations here on that particular syntax have still not worked.. the textboxes in the form are not populating. — Jonas Lear, Apr 04 '13 at 17:01

score 3 · Answer 1 · answered Apr 04 '13 at 16:57

3

It's because you got syntax error there due to nested, unescaped quotes. Try

$query = "SELECT * FROM users WHERE username = '" 
           . escape({$_SESSION['user']}) . "'";

or

$query = sprintf("SELECT * FROM users WHERE username = '%s'", 
           escape({$_SESSION['user']}));

And NEVER pass any external data to your queries without escaping (the escape() I used in examples is meta call. Depending on what you use to access DB you shall use correct method to do the job. It easy to find, as it contains escape in method name (i.e. mysqli_real_escape_string())

answered Apr 04 '13 at 16:57

Marcin Orlowski

72,056
11
123
141

I tried both of these and neither of them have worked. Here's that whole chunk of code: mysql_connect($host,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query = sprintf("SELECT * FROM users WHERE username = '%s'", escape({$_SESSION['user']})); $result=mysql_query($query); – Jonas Lear Apr 04 '13 at 17:05
The `{}` string notation is redundant when not in a `"`-quoted string context. – Marc B Apr 04 '13 at 17:14
Even with that taken out this didn't work. I don't know what the problem is here. – Jonas Lear Apr 04 '13 at 17:23

prodigitalson · Answer 2 · 2013-04-04T17:04:08.530

a user logs into a php form, authenticates with the backend mysql table, starts the session, carries them to the next page.

You need to start the session before any usage of it occurrs. So call session_start(); at the beginning of the script.

That query is also unsafe because the value from the session inst escaped. If you use PDO or Mysqli you can use a prepared statement to help you with that:

session_start();

if(isset($_SESSION['username'])) {

  $db = new PDO($dsn, $user, $pass);

  // the ? is a placeholder and will be replaced with the value supplied to
  // PDOStatement::execute
  $sql = 'SELECT * FROM users WHERE username = ? LIMIT 1';
  $stmt->prepare($sql);

  // Supply the value for the place holder and execute the query
  $stmt->execute(array($_SESSION['username']));

  // get the first and only row of data and close the statement cursor
  $userData = $stmt->fetch(PDO::FETCH_ASSOC);
  $stmt->closeCursor();

  if(!$userData) {
     // no user data handle appropriately
  }

} else {
    // redirect to login page?
}

How to correctly reference a PHP session variable in a SQL statement

2 Answers2