4 ms·

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

by 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.

by nbpoole 15y ago

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.