What is SQL injection? [duplicate]

Question

Can someone explain SQL injection? How does it cause vulnerabilities? Where exactly is the point where SQL is injected?

Herniorrhaphy

read up on Parameterized Queries – Hamal

@David A bit vague...but much clearer than it was when subu originally asked it. :-) – Paddy

@mezoid: true, my comment was actually referring to the original. It is much better now ;-) – Johnjohna

LOL sorry! I should have checked the time stamp! :-) – Paddy

This is SQL injection. – Brantbrantford

I love the recent attempts SO has been doing to take crap questions and turn them into valuable resources. Kudos, editors of this post! – Kipkipling

I don't agree that this is an exact duplicate of the question about the XKCD cartoon. – Cool

I agree with Bill Karwin. The linked question is asking for clarification of an XKCD comic. This question (the cleaned up version anyway) asks more specific questions and the answers here are better. – Alexaalexander

@Chris Thornton - How is it that ya'll aren't using parameterized queries? That would obviate the need.... – Arms

See also: unixwiz.net/techtips/sql-injection.html, securiteam.com/securityreviews/5DP0N1P76E.html, owasp.org/index.php/SQL_injection, en.wikipedia.org/wiki/SQL_injection, msdn.microsoft.com/en-us/library/ms161953.aspx, php.net/manual/en/security.database.sql-injection.php – Knop

@drachenstern - Legacy app, no changes allowed without a change request from the business unit, most database logic uses an abstraction layer so the business logic still thinks it's on a BTrieve database! – Hornmad

@Chris Thornton - Oh $DEITY that's really gotta suck. Ah well... – Arms

Answer 1

Can someone explain SQL injecton?

SQL injection happens when you interpolate some content into a SQL query string, and the result modifies the syntax of your query in ways you didn't intend.

It doesn't have to be malicious, it can be an accident. But accidental SQL injection is more likely to result in an error than in a vulnerability.

The harmful content doesn't have to come from a user, it could be content that your application gets from any source, or even generates itself in code.

How does it cause vulnerabilities?

It can lead to vulnerabilities because attackers can send values to an application that they know will be interpolated into a SQL string. By being very clever, they can manipulate the result of queries, reading data or even changing data that they shouldn't be allowed to do.

Example in PHP:

$password = $_POST['password'];
$id = $_POST['id'];
$sql = "UPDATE Accounts SET PASSWORD = '$password' WHERE account_id = $id";

Now suppose the attacker sets the POST request parameters to "password=xyzzy" and "id=account_id" resulting in the following SQL:

UPDATE Accounts SET PASSWORD = 'xyzzy' WHERE account_id = account_id

Although I expected $id to be an integer, the attacker chose a string that is the name of the column. Of course now the condition is true on every row, so the attacker has just set the password for every account. Now the attacker can log in to anyone's account -- including privileged users.

Where exactly is the point where SQL is injected?

It isn't SQL that's injected, it's content that's interpolated ("injected") into a SQL string, resulting in a different kind of query than I intended. I trusted the dynamic content without verifying it, and executed the resulting SQL query blindly. That's where the trouble starts.

SQL injection is a fault in the application code, not typically in the database or in the database access library or framework.

Most cases of SQL injection can be avoided by using query parameters. See How can I prevent SQL injection in PHP? for examples.

Cool

What about sql parameters. IMO it should be your first line of defense from sql injection ! Avoid injection content into sql string if possible. The content should go in parameters. This way DB knows that the content is not a part of sql and therefore you are automatically safe. – Airliner

@Petar Repac: Yes, query parameters are good and I recommend using them. But a parameter substitutes only for a single value. You can't use parameters for table or column identifiers, or SQL keywords, or lists of values in an IN() predicate, or other SQL expressions or syntax. Parameters are useful but you need other techniques for other cases. – Cool

Answer 2

SQL Injection occurs when the user of an application is able to affect the meaning of database query. This often occurs when arbitary strings from user input are concatenated to create SQL which is fed to the database. For example lets say we had the following code (in PHP, but the same holds true for any language), which might be used to handle a user login.

$sql = "SELECT  FROM users WHERE username='".$_GET['username']."' AND password='".$_GET['password']."'";

The harm is done when the user enters something like

administrator'; --

... for the username. Without proper encoding the query becomes:

SELECT * FROM users WHERE username='administrator'; -- AND password=''

The issue here is that the ' in the username closes out the username field then the -- starts a SQL comment causing the database server to ignore the rest of the string. The net result is the user can now log in as the administrator without having to know the password. SQL Inection can also be used to execute UPDATE, DELETE or DROP queries and really damage the database.

SQL Injection can be prevented by using parameterised queries, or applying your language/toolkit's escaping functions (such as mysql_real_escape_string() in PHP).

Once you understand SQL Injection you'll get the joke behind this cartoon.

Soutache

Answer 3

This question has been answered many times on StackOverflow, but it's an important topic for everyone to know about, so I'm not going to vote to close this question.

Here are links to some of my past answers on this topic:

• What is SQL Injection?
• How do I protect this function from SQL injection?
• Are Parameters really enough to prevent Sql injections?
• Is SQL injection a risk today?

I also gave a presentation at the MySQL Conference this month, and my slides are online:

• SQL Injection Myths & Fallacies

Cool

Answer 4

SQL injection is when things that're supposed to be data are treated as SQL code unwillingly.

For instance, if you were to do

mysql_query("SELECT * FROM posts WHERE postid=$postid");

Normally it'd get you the post with a given id, but assume that $postid is set to the string 10; DROP TABLE posts --; all of a sudden, the actual query you're sending is

mysql_query("SELECT * FROM posts WHERE postid=10; DROP TABLE posts --");

This is quite a problem, as you'd be losing your entire posts table due to a malicious user - oh dear.

The easiest way to prevent this is to use prepared statements, for instance through PDO or MySQLi.

The equivalent example in PDO would then be

$statement = $db->prepare('SELECT * FROM posts WHERE postid = :postid');
$statement->bindValue(':postid', $postid);
$statement->execute();

Doing this ensures that the database system knows that $postid is to be treated as data and not code, and will thus be handled appropriately.

Lions

Answer 5

SQL injection is where a malicious user will put SQL into input fields to try and run the SQL on your server.

The #1 advice that I adhere to is to use parameterized stored procedures rather than building raw SQL in code.

Stored Procedure parameters don't get executed, making them safe in most cases.

Diglot

Answer 6

I found this paper to be an extremely good read about SQL injection techniques (link is to PDF): Advanced SQL Injection In SQL Server Applications.

Despite the title saying "Advanced", it's quite readable even if you don't have much knowledge about SQL injection.

Quatrefoil

@johnmortal: Thanks, I replaced the link with a working one. My avatar is the main character from Persona 3. – Quatrefoil

Answer 7

To get some general background check out the Wikipedia article on SQL Injection.

In short SQL injection attacks can leave you vulnerable to all manor of database data theft and destruction. The exact details of what can be done to your system depend on the details of the system itself.

Any time you pass input from your users to your database you have a potential injection point. Web applications are often lacking in the this regard, as new programmers often do not understand the risks of handling input from users, and web applications are attacked by very smart people you never thought would find your program.

Shadbush

Answer 8

You will like this article from code project ; )

Summary

• Encrypt sensitive data.
• Access the database using an account with the least privileges necessary.
• Install the database using an account with the least privileges necessary.
• Ensure that data is valid.
• Do a code review to check for the possibility of second-order attacks.
• Use parameterised queries.
• Use stored procedures.
• Re-validate data in stored procedures.
• Ensure that error messages give nothing away about the internal architecture of the application or the database.

Governorship

lol second order attacks – Peremptory

hahahahah yes I know. but it is a set of variables that should be considered ; ) – Governorship

Answer 9

The point where SQL is injected is any point that your application accepts input from the user.

Whether this becomes a dangerous vulnerability for your web application depends on whether this input is later used as part of an SQL query without properly checking its type and escaping it if necessary.

Without proper escaping, some SQL code 'injected' by the user could be executed by the SQL engine as SQL code, rather than a simple string or value.

Eu