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

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

Solution 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.

Solution 2:

I cannot resist aswell.

SQL Injection is "a code injection technique that exploits a security vulnerability occurring in the database layer of an application". In other words it's SQL code injected in as user input inside a query.

SQL Injections can manipulate data (delete, update, add ecc...) and corrupt or delete tables of the database. I'm not aware of SQL Injections manipulating scripts though.

Let's say in your PHP script you are expecting (as user input) a username and a password from the login form that are later used inside a query such as:

SELECT Id FROM Users WHERE Name = $name AND Password = $password;

The user can insert inside $name and as $password whatever he likes (for example trough an <input>). Let's imagine he adds a name such as "1 OR 1 = 1; --", the query will now look like:

SELECT Id FROM Users WHERE Name = 1 OR 1 = 1; -- AND Password = $password;

and then, after the ; I could add another query or make the script think that the username and the password actually exists.

Notice that -- AND Password = $password; is a SQL comment and will therefore be ignored.

If you are using PHP < 5 then you should look for mysql_real_escape_string() and use it to escape user inputs before embedding it inside a query.

If you are using PHP5+ you should use PDO or the mysqli extension which can prevent this problem via prepared statements.

Solution 3:

I cannot resist posting this.

1- Sql Injection is explained better in one cartoon, than most other documents.

2- Mostly it does not do much to the server, but only to the underlying data. Consequence include delete, insert , select records, drop, create tables. (based on permissions etc..)

3- Examples.

4- Sorry I do not know PHP. But as long as you can abstract your DB layer from your View, you should be fine.

Solution 4:

There's a lot of information out there (and elsewhere in here) about this subject, so do not take this answer as a complete list by any means and continue to research on your own...

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

1. SQL injection is where an attacker discovers that an input value supplied to your application is being sent directly to a database and realizes that they can craft that input to be a custom SQL command. It could be something as simple as entering a special character (such as %) into a text field and receiving a strange response.

2. It can do anything your database allows that command to do. For example, if your web application has DB owner permissions for the application's database then an attack can potentially drop tables or even drop the whole database. Or, with even normal application permissions, the attack can over-write data or read sensitive data (such as plain text passwords if you have those).

3. For example, if an application has a text field where you enter a username. If that field is open to SQL injection, an attacker can enter something like: MyName';DROP TABLE Users;-- In this example, the attack manually finishes the query with the closing single quote and semi-colon, then adds another query, then comments out anything afterward. If not protected against this, the database may run both queries.

4. This one I don't know updated enough information, but there's lots out there :)