5 ms·

SQL Injection Pocket Reference (Google Doc)

112 points by rudenoise 15y ago

nbpoole 15y ago
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-subqueries.html http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-sub...: "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 15y ago
  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
- nbpoole 15y ago
  Whoops. I totally misunderstood what that part of the guide was trying to accomplish. In case anyone makes the same mistake I did, the suggestion is to get the column count by reading the error message. I'm used to not being able to read the error.
NY_Entrepreneur 15y ago
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?
- cleverjake 15y ago
  I've worked at a datacenter, partially as a disaster recovery guy for customers. 99% of the time, its no/improper input sanitation.
- khafra 15y ago
  > I intend to look at the data in the strings and see if it looks anything like a SQL command How will you do this? The only certain way I can think of is having an extra copy of the database you're using with a little "dummy" data in it, and testing user input against that to see if it changes data it shouldn't. But that would be computationally expensive. If you're checking with a regex or something, there's 1,001 ways to get around that. There are guidelines for SQL injection prevention in .NET[1], but what you describe ain't them. [1]http://msdn.microsoft.com/en-us/library/ff648339.aspx http://msdn.microsoft.com/en-us/library/ff648339.aspx
  - NY_Entrepreneur 15y ago

112 points by rudenoise 15y ago

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-subqueries.html http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-sub...: "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 15y ago
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
nbpoole 15y ago
Whoops. I totally misunderstood what that part of the guide was trying to accomplish. In case anyone makes the same mistake I did, the suggestion is to get the column count by reading the error message. I'm used to not being able to read the error.

NY_Entrepreneur 15y ago

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?

cleverjake 15y ago
I've worked at a datacenter, partially as a disaster recovery guy for customers. 99% of the time, its no/improper input sanitation.
khafra 15y ago
> I intend to look at the data in the strings and see if it looks anything like a SQL command How will you do this? The only certain way I can think of is having an extra copy of the database you're using with a little "dummy" data in it, and testing user input against that to see if it changes data it shouldn't. But that would be computationally expensive. If you're checking with a regex or something, there's 1,001 ways to get around that. There are guidelines for SQL injection prevention in .NET[1], but what you describe ain't them. [1]http://msdn.microsoft.com/en-us/library/ff648339.aspx http://msdn.microsoft.com/en-us/library/ff648339.aspx
- NY_Entrepreneur 15y ago