1

Basically I am making a web application and I am going through the security of it to make my app as robust as I can.

Once you're logged in to my app I track that user based on session variables.

When SQL is performed it takes the users session variable to see who they are for example. 

 $name = $_SESSION['user_name'];

A example query would be something like this..

 $query1 = "SELECT * FROM tableName WHERE userName = '$name'";

From reading online sites say things like I must not use "user input in SQL statements." Am I right in thinking that because I am using session variables the user does not have direct access to the sql statement or is session variables still "user input"?

If not should I just go through the normal SQL Injection prevention methods like...

  • Input validation (authenticating the data based on length, syntax etc)
  • Checking user privileges making sure users have the least privileges.
  • ect..

Thanks in advance for any comments anyone makes.

rm_
  • 35
  • 1
  • 7

5 Answers5

4

It doesn’t matter where the data came from as in any case you have to ensure that the data is interpreted as intended.

And one of the easiest way to ensure that is using parameterized statements, where the SQL code and parameter values are passed separately so that a parameter value can’t be mistakenly interpreted as SQL.

Prepared statements implement this parameterization, either native or emulated. With them you don’t have to worry about whether a value may be influenced by a user’s input.

Community
  • 1
  • 1
Gumbo
  • 643,351
  • 109
  • 780
  • 844
2

Always escape input. What if their name is "David O'Connor" or something.

This name is assumedly user supplied, so do it.

Anyway, you shouldn't be directly issuing SQL queries, a function should be building them for you (or an ORM).

Rich Bradshaw
  • 71,795
  • 44
  • 182
  • 241
0

Am I right in thinking that because I am using session variables the user does not have direct access to the sql statement or is session variables still "user input"?

The problem you might have in your example is that the username might be something controller by the user. They might be able to change it or choose it depending on your application requirements.

I think the simplest way to prevent surprises is stop thinking too much and just protected your queries agaisnt SQL Injection.

I'm no expert on PHP, but I think you have prepared statements that will help you dealing with this security issue. Just use it. (it should be a slogan, "prepared statements, just use it." :D)

Bruno Costa
  • 2,708
  • 2
  • 17
  • 25
0

Any login/create-user page you have should do input validation before writing the userName column in your database and writing $_SESSION['user_name'].

AFTER that, your $_SESSION variable is safe. You have to do validation checks somewhere in the process:

Input -> store in database/session -> use

I recommend at the 1st arrow. Then the code snippets you wrote in the question are perfectly fine.

AvadData
  • 819
  • 1
  • 6
  • 11
-1

Am I right in thinking that because I am using session variables the user does not have direct access to the sql statement

correct. $_SESSION-vars are stored on your server-filesystem and unless you introduce user-input ($_GET & $_POST, for example) into them there is no actual way of manipulation for these. If you generate $_SESSION['user_name'] without actual userdata you're well off. The hard part is getting the username without parsing input - one would be advised to use HTTP-data-filtering frameworks for these kinds of jobs.

specializt
  • 1,913
  • 15
  • 26
  • The way I am getting the username across the pages is via making session variables when you do an action like $_POST I just get the user id from the session variable is this safe? – rm_ Jul 05 '14 at 14:41
  • well .. that depends - how do you generate the user id? – specializt Jul 05 '14 at 14:48
  • sorry I meant the username, when you log in I do checks to make sure the username is valid then it gets put into $_SESSION['user_name'] and that variable is passed across all pages while you're logged on – rm_ Jul 05 '14 at 14:55
  • aaand thats where your security risk is : the username validation check. Make sure the input is escaped (as already mentioned); in fact, i think generating a hashcode of your escaped (!) input and comparing it to hashes of usernames in your DB is an acceptable workaround - its merely impossible to exploit hashes :) – specializt Jul 05 '14 at 15:02