CODEWITHSUNDEEP
mysqlsqlsecuritysql-injection
Michael Samuel
Michael Samuel

Reputation: 3920

MySQL "INSERT" and SQL injection

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?

Upvotes: 4

Views: 3755

Answers (4)

tadman
tadman

Reputation: 211720

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.

Upvotes: 1

Chris Stillwell
Chris Stillwell

Reputation: 10547

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.

Upvotes: 2

frlan
frlan

Reputation: 7270

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)

Upvotes: 6

Caff&#233;
Caff&#233;

Reputation: 1171

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.

Upvotes: 2

Related Questions