Do I need to escape session variables to prevent SQL injection?

[kthnx]

Hey guys,

Every time I accept a user supplied variable, I whack it inside a mysql_real_escape_string function because of people like this.



So every time there's a bit of POST data in my PHP, it'll look like this:

PHP Code:

$username=mysql_real_escape_string($_POST['user_name']); 


However, it's recently occured to me that people could launch a similar attack by changing the contents of their session variables. Rather than storing a session ID, I've just got the user storing the username / password in a session and re-authenticating every time a new page loads; that way you can effectively kick a user from an active session by changing their password.

PHP Code:

if (isset($_SESSION['UID'])) {
    $username = $_SESSION['username'];    
    $password = $_SESSION['password'];
    $result = mysql_query("SELECT * FROM users WHERE Username = '$username'");
    $row= mysql_fetch_array($result);
    $dbPassword = $row['Password'];
    if (($dbPassword != "") && ($password == $dbPassword)) { 
        $authenticated = true;
    } else {
        // Unauthenticated user!
            session_destroy();
                session_write_close();
        header("Location: login.php");
    }
} 


Should I be using a mysql_real_escape_string on each of those session variables too? Is it easy for malicious hackerz to be tampering their session contents to SQL commands?

Thanks for any advice!

[Ashpool]

Should I be using a mysql_real_escape_string on each of those session variables too?

Yes, I would

Is it easy for malicious hackerz to be tampering their session contents to SQL commands?

Never tried it, but it doesn't sound like it would be hard to do.

If you want it really tight you probably should look at parameterized statements

From the wiki
In PHP version 5 and above, there are multiple choices for using parameterized statements. The PDO database layer is one of them:

$db = new PDO('pgsql:dbname=database');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();

There are also vendor-specific methods; for instance, using the mysqli extension for MySQL 4.1 and above to create parameterized statements:

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

[Luke212]

to change server side session variables they need access to your system (or file system). in other words worrying about sessions would the least of your concerns.

[kthnx]

Originally Posted by Luke212

to change server side session variables they need access to your system (or file system). in other words worrying about sessions would the least of your concerns.

Are the examples in my code above server side? I thought all that was handled by the browser :s

[Luke212]

Originally Posted by kthnx

Are the examples in my code above server side? I thought all that was handled by the browser :s

you will notice all code referring to session variables is in php. php runs on the server. the browser doesnt even know what php is. so yeah session variables are server-side.

something you dont see, i believe a cookie is created on the browser. the cookie does not contain server varables, but it does contain a key so php can match a client to a particular server session.

[mordy]

I'm really confused how the hacker can change the session vars ...

They can change their sessionid ... hence losing their session, but not make a changes to it

(luke, i'm glad your here, i thought i was losing it ... more than usual)

[kthnx]

Ohh I see! I knew something session related was stored at the client end (either in a cookie or the URL, depending on the browser) but that's just a key to access session variables, which are all stored server side?

That's going to save me five lines of code and an unnescessary SQL query

[kthnx]

Originally Posted by Ashpool

If you want it really tight you probably should look at parameterized statements

From the wiki
In PHP version 5 and above, there are multiple choices for using parameterized statements. The PDO database layer is one of them:

$db = new PDO('pgsql:dbname=database');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();

There are also vendor-specific methods; for instance, using the mysqli extension for MySQL 4.1 and above to create parameterized statements:

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

Trying to set this up now because it looks like a challenge is it likely to put more load on my server than a traditional query? I'm just debugging at the moment but eventually there's supposedly going to be quite heavy traffic once it goes live.

[mordy]

Originally Posted by kthnx

Ohh I see! I knew something session related was stored at the client end (either in a cookie or the URL, depending on the browser) but that's just a key to access session variables, which are all stored server side?

yep, its like you ordering from a fast food place and being given token 13 ... the token is not your order, but you can go back to the counter to pick up your order with that token.

Similarly, on every request, the client sends his token to the server, which allows the server to keep track of data throughout the session. Each session cookie has a $_SESSION saved on the server. Otherwise the web would suck as it would be completely stateless ... think web 0.5

[Ashpool]

Originally Posted by kthnx

Trying to set this up now because it looks like a challenge is it likely to put more load on my server than a traditional query? I'm just debugging at the moment but eventually there's supposedly going to be quite heavy traffic once it goes live.

In general parameterized queries are faster! Sometimes a lot faster!

Why? The theory is that the query is compiled only once and the server holds on to the compiled plan for multiple uses.

It's my understanding parameterized queries are the way to go and accepted as current best practices.

Looking back now yes it makes sense that the session variables are server side only. SQL injection is pretty common, imagine if hackers could modify the variables then we would have a shit load more of these attacks.

[mordy]

Originally Posted by Ashpool

In general parameterized queries are faster! Sometimes a lot faster!

Why? The theory is that the query is compiled only once and the server holds on to the compiled plan for multiple uses.

It's my understanding parameterized queries are the way to go and accepted as current best practices.

It really depends what the search is "select * from table" is not gonna be faster ... it happens when there are complex joins, and the DBMS has to compile a path of execution ... the retrieval is only as good as the indexes and the way you do your joins and the DBMS etc.

[Luke212]

Originally Posted by mordy

It really depends what the search is "select * from table" is not gonna be faster ... it happens when there are complex joins, and the DBMS has to compile a path of execution ... the retrieval is only as good as the indexes and the way you do your joins and the DBMS etc.

select * from table doesnt have any parameters so there is only one way to do it. parameters will virtually never be slower and there is no reason not to use them.

[Elyzion]

Originally Posted by Ashpool

In general parameterized queries are faster! Sometimes a lot faster!

They are only faster if the DBMS caches the query.

If you are using a DBMS that caches queries and you're not using parameterized queries then it compiles the query every time its executed. Which makes it slower.