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
– Keeleon, Commented Apr 4, 2013 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
– Mike Christensen, Commented Apr 4, 2013 at 16:57
Possible duplicate of How to prevent SQL injection in PHP? Session variables are still variables. — Álvaro González
– Álvaro González, Commented Apr 4, 2013 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
– Mike Brant, Commented Apr 4, 2013 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
– Jonas Lear, Commented Apr 4, 2013 at 17:01

Marcin Orlowski · Accepted Answer · 2013-04-04 16:57:15Z

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 4, 2013 at 16:57

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonas Lear Over a year ago

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);

Marc B Over a year ago

The {} string notation is redundant when not in a "-quoted string context.

Jonas Lear Over a year ago

Even with that taken out this didn't work. I don't know what the problem is here.

prodigitalson · Accepted Answer · 2013-04-04 17:04:08Z

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?
}

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related