

SQL Injection Pocket Reference (Google Doc) - rudenoise
https://docs.google.com/Doc?docid=0AZNlBave77hiZGNjanptbV84Z25yaHJmMjk&pli=1#Allowed_Intermediary_Character_30801873723976314

======
nbpoole
Noticed a small mistake in the MySQL section:

    
    
        Error Based:
            AND (SELECT * FROM SOME_EXISTING_TABLE) = 1
    

Unless SOME_EXISTING_TABLE has no more than one row, that query will fail. See
[http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-
sub...](http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-
subqueries.html):

" _For a comparison of the subquery to a scalar, the subquery must return a
scalar._ "

It's easy to fix: just add _LIMIT 1_ to the end of the sub-select.

\---

This document is very useful. I've been doing web app security for a while and
I haven't run across some of these techniques before (eg: PROCEDURE
ANALYSE()). I didn't realize that PDO_MYSQL allowed for multiple queries by
default either (although it makes sense, since historically other RDBMS have
supported it).

~~~
nbpoole
There's actually another issue that I missed before. SELECT * returns all of
the columns in the table: you can't compare that to a scalar.

Tested on MySQL 5.5 on OS X:

    
    
        mysql> use test
        Database changed
    
        mysql> show tables;
        Empty set (0.00 sec)
    
        mysql> create table test (id int(10) unsigned not null auto_increment, name varchar(10), primary key(id));
        Query OK, 0 rows affected (0.13 sec)
    
        mysql> insert into test (name) values ("Bob");
        Query OK, 1 row affected (0.02 sec)
    
        mysql> select * from test;
        +----+------+
        | id | name |
        +----+------+
        |  1 | Bob  |
        +----+------+
        1 row in set (0.00 sec)
    
        mysql> select * from test WHERE (select * FROM test) = 1;
        ERROR 1241 (21000): Operand should contain 2 column(s)
    
        mysql> select * from test WHERE (select 1 FROM test) = 1;
        +----+------+
        | id | name |
        +----+------+
        |  1 | Bob  |
        +----+------+
        1 row in set (0.00 sec)
    
        mysql> insert into test (name) values ("Frank");
        Query OK, 1 row affected (0.00 sec)
    
        mysql> select * from test WHERE (select 1 FROM test) = 1;
        ERROR 1242 (21000): Subquery returns more than 1 row

------
NY_Entrepreneur
HELP! I don't 'get it'!

Looking at that list of SQL injection attack techniques, I don't see the
threat.

Or: Yes, I'm building a Web site, and the server will be running SQL Server.

Some of my Web pages have users enter data in text boxes. So, they could enter
a SQL command in a text box.

Then when the Web page is returned to my Web server as a 'post back', my
software reads the data in the text boxes.

I have the page built, have code for extracting the data from the text boxes
and putting it in, say, string variables in Visual Basic .NET, and now am
writing the code for looking at the data in the strings from the text boxes.

So far, I see no threat.

So, I intend to look at the data in the strings and see if it looks anything
like a SQL command. Anything that looks like a SQL command will get rejected
as bad data and not get near my SQL Server database. Maybe I will write the
bad data to my log file.

So, I'm checking the input data from the users. I am assuming that the input
data could be anything at all until my code establishes otherwise.

Doesn't everyone do such checking?

With such checking, where is the threat of 'SQL injection'?

If SQL injection is a threat, then why? Just from people not checking input
data from users? Some other reason?

~~~
timwiseman
First, just checking the data is often not enough and can be hard to do right.
The better option normally is to paramaterize the query which makes it very
difficultto prevent it.

And no, not everyone paramaterizes or validates their data at all.

I wrote about some of the defensive techniques at [http://www.simple-
talk.com/sql/learn-sql-server/sql-injectio...](http://www.simple-
talk.com/sql/learn-sql-server/sql-injection-defense-in-depth/) a couple of
months ago.

~~~
mtogo
Normally i hate "This" posts, but HN does not display my upvote, so i'm
writing this comment to reiterate how important this is.

This is the right way to do it. Paramaterize your queries, do not _ever_ use
string concatenation to generate SQL queries. If you try to sanitize the
inputs yourself you will fail at some point.

~~~
NY_Entrepreneur
Let me be more clear here by what is meant by 'parameterizing' a SQL command.

So, looking at some of my old code I see in Visual Basic .NET, I start by
'declaring' the types of, and allocating storage for, three identifiers:

    
    
        Dim my_SQL_command_string As String
    
        Dim my_SQL_INSERT_command As New SQLCommand
    
        Dim new_instance_values_sum As Long
    

Then I build the 'command text' of a SQL command string:

    
    
        my_SQL_command_string = _
          " INSERT Instance_Value_Averages " & _
          " ( Instance_ID, Instance_Values_Sum, N_Instance_Values ) " & _
          " VALUES " & _
          " ( @Instance_ID, @Instance_Values_Sum, @N_Instance_Values ) "
    

In this string are three 'parameters':

    
    
          @Instance_ID
          @Instance_Values_Sum
          @N_Instance_Values
    

Then I assign my command string, with its three parameters, to the property
CommandText of my SQL command:

    
    
        my_SQL_INSERT_command.CommandText = my_SQL_command_string
    

My SQL command has a property Parameters which is an instance of a collection
class with a method Add. So, I use the method Add to add a value for parameter

    
    
        @Instance_Values_Sum
    

to the collection:

    
    
        my_SQL_INSERT_command.Parameters.Add( "@Instance_Values_Sum", _
          SqlDbType.bigint, 8 ).Value = new_instance_values_sum
    

I continue with such uses of the method Add and assign values to the other two
parameters

    
    
          @Instance_ID
          @N_Instance_Values
    

Eventually I execute the SQL command via:

    
    
        my_SQL_INSERT_command.ExecuteNonQuery
    

For the value I give to parameter

    
    
          @Instance_Values_Sum
    

it is 8 bytes of binary and, thus, could have anything that could be in 8
bytes. So, presumably my SQL query is fully safe from any data at all and also
from 'SQL injection'.

Is this what is meant by 'parameterizing' a SQL query?

