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

Solution 1

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 2

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 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 :)

View more solutions

Comments

[uncaught_exceptions]

did you check for duplicate questions???

[Doug Molineux]

this will be down voted, whenever I ask about SQL injection, everybody freaks out. In order to build a defense against you need to know how to exploit it! A locksmith needs to know how to pick his lock so that he can ultimately make it stronger

[alexyorke]

Dup: stackoverflow.com/questions/601300/what-is-sql-injection stackoverflow.com/questions/1819122/… stackoverflow.com/questions/3470415/… stackoverflow.com/questions/2216107/what-is-sql-injection

[Johan]

@doc_180 Yes I did and I did not find a single question where the title suggest even a 50% match with the contents of this question. S:-\ This unhappy ignorance of SQL-injection gotta stop. All the bad guys know about it.

[Marc B]

@johan: then learn to search better. Pretty much any question here involving databases will mention injection somewhere in the question and/or answers.

[Mixxiphoid]

How did the bad guys got to know? :) Google is your friend... SQL_injection

[Johan]

I know the answer, just want to have a resource to refer to and hoping there some new trick I can learn.

[Jim]

@Johan, the problem is, there are too many variables. For instance, how are you connecting to your database? PDO, mysql, mysqli...etc? What are you currently doing? Relying on magic_quotes? Using mysql_real_escape_string? Are you validating the data that comes in? Are you using prepared statements? A ton of factors come into to play when discussing the topic. If you narrow it down so it is not so broad, you would get a better response. Also marking this as community Wiki may help.

[Johan]

@k_to_the_z: I was hoping to get a link I can refer to to explain sql injection, 'cause I'm getting blue fingers from typing mysql_real_escape_string()

[k to the z]

Ha. en.wikipedia.org/wiki/SQL_injection

[Johan]

@k_to_the_z: from the article, the only protection advice is: A straightforward, though error-prone, way to prevent injections is to escape characters... Hardly airtight advice.

[uncaught_exceptions]

Sorry if answer is overly concise..

[Rodolfo Paranhos]

Your answer was the only one that made me clearly understand SQL injection. Thank you!