4

I have this simple mysql query:

INSERT INTO table (col1, col2) VALUES ('1', '2')

col1 and col2 are foreign keys for another table so any value for col1 and col2 must be present in the other table or otherwise the row won't be inserted.

Is there still any risk of SQL injection in this case? If i receive these col values from PHP POST, do I still need to bind them before insertion into the database or they are already secure as the cols are foreign keys?

frlan
  • 6,950
  • 3
  • 31
  • 72
Michael Samuel
  • 3,820
  • 12
  • 45
  • 85
  • 2
    Just use properly 'prepared' queries and a lot of the 'issues' go away. If you **always** used 'prepared' queries with 'dynamic' parameters then you will be 'safe'. You must 'validate' all input but if 'less than usuful stuff' gets into the database, it is not 'harmful'. I am not that concerned about 'efficiency' in the first instance. – Ryan Vincent Oct 16 '14 at 19:57
  • 2
    The ONLY way that this is acceptable, is if you KNOW the input cannot possibly have malicious code in it. Unfortunately there's no way of actually knowing that, if you're relying on end user input, or even existing database values for that matter. For all practical purposes, ALWAYS assume the end user is up to no good, and don't trust any input from them is clean. – user2366842 Oct 16 '14 at 19:58
  • Please read this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Andy Lester Oct 16 '14 at 21:20

4 Answers4

6

Yes. All input from users needs to be check for sanitized. E.g. if a user is sending you a string like that '2'); drop table <table> as your second value it might get executed and giving you some surprise. (String might not work exactly, but I think you got the point)

frlan
  • 6,950
  • 3
  • 31
  • 72
2

It's indeed prone to SQL Injection, since the user could, for example, break your query and get information about your RDBMS and your database schema in the error message, and use it to prepare another attacks to your applications.

There are a lot of ways to explore SQL Injection issue.

Caffé
  • 1,161
  • 1
  • 9
  • 19
2

Yes, there is always a risk of injection, even with foreign key constraints. If I know what is a valid col1 and col2 value, I can use those values to construct an attack. It is best to always scrub user input and assume the user is trying to hurt your database.

Chris Stillwell
  • 10,266
  • 10
  • 67
  • 77
  • +1. How difficult this is to pull off may be a different topic entirely (probably easier to pull off if the foreign keys are numeric as opposed to GUID/UUID's for example), but it's much easier to just properly deal with the data insertion. – user2366842 Oct 16 '14 at 20:09
  • Well it depends, if the keys are ever exposed somewhere, like in a drop down list, than it would be easy no mater what format they are in. But, you are right, if the keys are never shown outside the server ad GUID/UUID would be considerably harder to crack. If I don't know they keys, but can get a table name on the constraint by generating an error, I can do a "INSERT INTO table (col1, col2) VALUES ('1', (SELECT col2 FROM )); DROP TABLE...."
     – Chris Stillwell Oct 16 '14 at 20:12
1

When constructing database queries in PHP, use an interface that allows you to use placeholders for your data that will handle any escaping automatically. For example, your query would look like:

INSERT INTO table (col1, col2) VALUES (:col1, :col2)

Then you can bind against that using the appropriate method in a driver like PDO. If you're disciplined about using placeholders for user data, the chance of a SQL injection bug occurring is very low.

There's a number of ways to properly escape user input, but you must be sure to use them on all user data, no exceptions. A single mistake can be sufficient to crack your site wide open.

tadman
  • 208,517
  • 23
  • 234
  • 262