CODEWITHSUNDEEP
mysqlescaping
Tyilo
Tyilo

Reputation: 30102

Escape mysql table name

How do I escape a table name named log\'; WAITFOR DELAY \'0:0:5\';--?

I want to delete the table.

None of these works:

SHOW TABLES LIKE 'log\\\'; WAITFOR DELAY \\\'0:0:5\\\';--';
SHOW TABLES LIKE "log\\'; WAITFOR DELAY \\'0:0:5\\';--";
SHOW TABLES LIKE `log\\'; WAITFOR DELAY \\'0:0:5\\';--`;

The last one gives an error, while the others gives no results. The error is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`log\\'; WAITFOR DELAY \\'0:0:5\\';--`' at line 1

Upvotes: 2

Views: 2003

Answers (3)

spencer7593
spencer7593

Reputation: 108380

These statements will return the tablename (if the table exists):

SHOW TABLES LIKE "log\\\\'%"

SHOW TABLES LIKE "log\\\\'; WAITFOR DELAY \\\\'0:0:5\\\\';--"

SELECT table_name FROM information_schema.tables 
 WHERE table_name LIKE "log\\\\'; WAITFOR DELAY \\\\'0:0:5\\\\';--"

To reference the table name in a SQL statement, you'll need to use backticks to enclose the name, for example:

SELECT 1 FROM `log\'; WAITFOR DELAY \'0:0:5\';--` LIMIT 1 ;

RENAME TABLE `log\'; WAITFOR DELAY \'0:0:5\';--` TO `foo` ;

DROP TABLE `log\'; WAITFOR DELAY \'0:0:5\';--` ;

NOTE: The backslashes and special characters in the name don't need to be escaped when the object name is referenced in a SQL statement, just enclose it in backticks. But those backslashes DO need to be escaped when it's being interpretted as a string literal, as it is in the LIKE predicate.

(I've been there and and done that, creating a wonky table names.)

To drop all tables with names starting with log\', I would do it as a two step process. First, I would generate DROP TABLE statements, and then I would execute those statements.

SELECT CONCAT('DROP TABLE `',table_name,'`;') 
 FROM information_schema.tables
WHERE table_name LIKE "log\\\\'%"
  AND table_schema = DATABASE()

Upvotes: 2

craig65535
craig65535

Reputation: 3572

Does the table name actually contain \ characters, or is it just log'; WAITFOR DELAY '0:0:5';--? Because according to http://dev.mysql.com/doc/refman/5.0/en/identifiers.html \ characters are not allowed in table names.

Does this work:

SHOW TABLES LIKE `log'; WAITFOR DELAY '0:0:5';--`;

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

`log\'; WAITFOR DELAY \'0:0:5\';--`

try that.

EDIT

Edited from 

`log\\'; WAITFOR DELAY \\'0:0:5\\';--`

to

`log\'; WAITFOR DELAY \'0:0:5\';--`

Upvotes: 0

Related Questions