1

I am wondering if anything from the $_SESSION array needs to be escaped before I use it in a SQL query.

Note that I don't use cookies in my application, since I've heard that they could be used for session hijacking (?)

Thanks a lot

alexx0186
  • 1,557
  • 5
  • 20
  • 32

5 Answers5

5

You need to escape every string you pass to the sql query, ragardless of its origin.

Even if it is the data you retrieved from your database.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • Thanks for your response. I think I will use prepared statement for that, but I wanted to know if that SESSION array could be a security threat. Thanks again – alexx0186 May 18 '12 at 00:13
  • 4
    @alexx0186: not actually security thing, but mostly related to the sql syntax correctness. What if there is a quote `'` in the text value you retrieved from database. If you use it as-is - you would get your query broken. So think of escaping as of a way to write sytactically correct query, not a security thing – zerkms May 18 '12 at 00:15
  • 1
    @alexx0186 - this may provide further insight to prevent session hijacking: http://stackoverflow.com/questions/5081025/php-session-fixation-hijacking – J Young May 18 '12 at 00:16
2

On the assumption that there are yet to be revealed exploits in PHP, everything should be escaped using prepared statements or mysql_real_escape_string before you allow anything to touch your database.

Data stored in $_SESSION is not always clean. For multi page forms you may store user input in $_SESSION until the final page when you write it all into the database. If you get into any kind of habit of thinking $_SESSION is "clean" you will eventually get yourself in trouble.

You should absolutely get into the habit of assuming every piece of data in your system is dirty until you have escaped it. Note, if you are using dynamic table names, escaping doesn't help you. Never use table or column names in a query that have ever gone anywhere near a user. The various escaping mechanisms don't escape backticks. If you have a prepared query of say: 

"SELECT * FROM `:aTable`;"

and aTable comes from a user, a user that enters something like 

` WHERE id IN (DELETE FROM user);

has potentially just deleted all your user records.

Endophage
  • 21,038
  • 13
  • 59
  • 90
  • Hi, thanks for your response. I am looking at your example and wondering if not using backticks would prevent this kind of exploit? Thanks – alexx0186 May 18 '12 at 00:22
  • Also, I don't understand what you mean by "Never use table or column names in a query that have ever gone anywhere near a user". It seems inevitable to me to use a column name in query that contains user input – alexx0186 May 18 '12 at 00:23
  • `has potentially just deleted all your user records.` --- in what DBMS nested `DELETE` is a valid syntax? – zerkms May 18 '12 at 00:30
  • @alexx0186: "It seems inevitable to me to use a column name in query that contains user inpu" -- he meant - use user's input only for substituting values, not column/table names – zerkms May 18 '12 at 00:31
  • @zerkms ok maybe not an outer select statement, but I believe at least in mysql you can do a delete as a subquery in an insert/update statement... Haven't tested that but the documentation on subqueries suggests it would be valid. – Endophage May 18 '12 at 01:27
  • @Endophage: it's not valid at least in mysql and postgresql. Don't have sql server and oracle to test, but I bet you'd get the same results – zerkms May 18 '12 at 01:58
1

A $_SESSION variable is the same as a $_GET variable if used incorrectly, so the answer to your question is yes, if your storing RAW user inputs in a session (which you shouldn't be doing) then you would need to escape it.

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106
  • Hi, thanks for your response. Actually, everything I store in thie SESSION array comes from the database, when the user logs in. Regards – alexx0186 May 18 '12 at 00:11
  • 1
    You also need to escape things you get from the database. You need to escape everything. To put it another way, here is a list of things you don't need to escape: – Okonomiyaki3000 May 18 '12 at 00:17
  • Then you should know what values your adding to the session, 99.9% of scripts use sessions to store info retrieved from a database. Just dont store RAW user submitted data that intracts with your database or output, you should always sanitize output with htmlentities and use prepared query's for database calls. Simplez – Lawrence Cherone May 18 '12 at 00:19
  • Thanks for that. So I assume I should use htmlentities, before a prepared statement, and this should be good enough, I guess. Thanks – alexx0186 May 18 '12 at 00:26
1

Session variables are just like any other variables. The data in there have to come from somewhere. if you directly store a posted variables there, then it is basically like using the posted variable.

The only diff is that a session variable persist across different access, and that is about it.

iWantSimpleLife
  • 1,944
  • 14
  • 22
1

One golden rule is never trust user input furthermore unless the data has originated from you (i.e. your system) it should be considered 'user input', and this most certainly includes session data.

It terms of escaping session data for SQL, you can and should effectively clean the data for sql use, such as using mysql_real_escape_string() but depending on what data is contained within the session I would also validate the session against what you expect it should contain.

Not too sure on what you mean in regards to the cookie / session hijacking comment, I assume you mean you only use session's to store data? In a typical php installation sessions still use cookies purely as a pointer to the user's session.

Steve H
  • 561
  • 3
  • 5