How does SQL-injection work and how do I protect against it

Question

Possible Duplicate:
What is SQL injection?

I see a lot of php code floating around on stackoverflow and (too) little escaping of strings.

Can anyone

1. Explain what SQL injection is;
2. Explain what it can do to your server, data and code;
3. Give an example how to perform an SQL-injection
4. Give php sample code how to protect against SQL-injection

Answer 1

An SQL injection is a maliciously formed SQL query used to "confuse" an SQL database into giving something it shouldn't. For instance, consider the following query

"SELECT * FROM `users` WHERE `username` = '$name'";

In a normal case, this will work. If we submit 'Jack' to this, it will return all users named Jack. However, if a user enters, say "' OR 1=1", the resulting query would be

"SELECT * FROM `users` WHERE `username` = '' OR 1=1";

Since 1 always equals 1, and the combinating clause is OR, this will return true on every row, which will in turn display EVERY row to the malicious user. Using this technique, someone can view your entire database. Also consider if someone submits something like "'; DROP TABLE users";--, which results in

"SELECT * FROM `users` WHERE `username` = ''; DROP TABLE `users`";--";

Which is two queries, one which will do nothing, the second which will delete the ENTIRE users database, resulting in the loss of your data.

The best method to prevent SQL injections is to use prepared statements. With these, you send a query to the SQL database that says something like

"SELECT * FROM `users` WHERE `username` = '?'";

This lets the database know the format of the query (WHERE username equals some value), so there is no confusion when given a plain text query. Then the database knows to expect one value, and where to put it. Then you pass that value to the database which it can use to search. This is also better as the database can optimize the query for faster searching.

Read up on prepared statements, which will explain this in more detail.