Clever Geek Handbook

MySQL "INSERT" and SQL injection

I have a simple mysql query:

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

col1

and col2

are foreign keys to another table, so any value for col1

and col2

must be present in another table or else the row will not be inserted.

Is there a risk of SQL injection in this case? If I get these col values ​​from PHP POST, do I still need to bind them before inserting into the database, or are they already secured since cols are foreign keys?

+3


source to share


4 answers


Yes. All user input must be checked for disinfection. For example. if the user sends you a string of type '2'); drop table <table>

as your second value, it can get done and give you some surprise. (The line may not work exactly, but I think you get the point)



+6


source


It is indeed susceptible to SQL Injection, since the user can, for example, rip your query and get information about your RDBMS and your database schema in an error message and use it to prepare other attacks for your applications.



There are many ways to study the problem of SQL injection.

+2


source


Yes, there is always the risk of injection, even with foreign key constraints. If I know what is the valid value of col1 and col2, I can use those values ​​to build an attack. It's best to always flush user input and assume that the user is trying to corrupt your database.

+2


source


When building database queries in PHP, use an interface that lets you use placeholders for your data that will handle any escaping automatically. For example, your request would look like this:

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

Then you can bind it to the appropriate method in a driver like PDO. If you are disciplined about using placeholders for custom data, the chances of an SQL injection error occurring are very low.

There are several ways to properly undo user input, but you should definitely use them in all user data, no exceptions. One mistake can be enough to hack your site as a whole.

+1


source






More articles:


All Articles