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?

Improve this question
3
  • 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. Commented Oct 16, 2014 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. Commented Oct 16, 2014 at 19:58
  • Please read this: stackoverflow.com/questions/60174/… Commented Oct 16, 2014 at 21:20

4 Answers 4

Reset to default
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)

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

2 Comments

I don't think sanity is the word your looking for. All input from users needs to be sanitized.
Stupid froreign language. But at least it's close ;) Updated Answer.
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.

Improve this answer

Comments

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.

Improve this answer

2 Comments

+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.
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 <table>)); DROP TABLE...."
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.