Escape table name MySQL

Question

I have a little problem with escaping table name. I was so stupid that i choose "show" for the name of table. When I use mysqli connection the escaping works fine, but its not working with classical mysql connection. Any advise? Sorry for my English, I am not native speaker.

SELECT SQL_CALC_FOUND_ROWS year, nameShow 
FROM   `show`
LIMIT 0, 10

I get error as

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 'show' at line 2 –

Query

$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS year, nameShow 
    FROM   `show`
    $sWhere
    $sOrder
    $sLimit
    ";

When you do this mistake always fire [RENAME TABLE tbl_name TO new_tbl_name](http://dev.mysql.com/doc/refman/5.0/en/rename-table.html) command and give proper name to table :) — Fahim Parkar, Jun 12 '12 at 21:00
I got this error `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 'show' at line 2` — zdarsky.peter, Jun 12 '12 at 21:09
You can try to prefix the from with the database name (no need for backticks then). `FROM dbname.show` — hafichuk, Jun 12 '12 at 21:10
I can not rename the table, because its running project and I will have to rename it and find it in the code. — zdarsky.peter, Jun 12 '12 at 21:10
I tried the prefix before I was posting the problem here. Its still not wokring. `SELECT SQL_CALC_FOUND_ROWS year, nameShow FROM serialtracker.show ORDER BY year asc LIMIT 0, 10 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 'show' at line 2` — zdarsky.peter, Jun 12 '12 at 21:13
when I ran your query, I didn't face any problem in MY SQL 5.5 [see here](http://sqlfiddle.com/#!2/c03cf/1) — Fahim Parkar, Jun 12 '12 at 21:26
Its individual filtering, but in this case its empty. I am using Datables [link](http://datatables.net/release-datatables/examples/data_sources/server_side.html) . The code is from example on the bottom of the page. — zdarsky.peter, Jun 12 '12 at 21:28
I dont have problem with query when I copy the printed sql from browser and run it with phpmyadmin. — zdarsky.peter, Jun 12 '12 at 21:30
Finally I have got working query. I dont know why the others solutions werent working. But this works : `serialtracker.\`show\`` — zdarsky.peter, Jun 12 '12 at 21:42
FWIW years later, perhaps no database had been set. E.g. `USE serialtracker;` before your query. That is, the error message may have been misleading in this circumstance. — ToolmakerSteve, Jul 19 '20 at 02:21

xelco52 · Accepted Answer · 2012-06-12T21:18:55.553

11

Section 9.3 of MySQL 5.1 Reference Manual says back ticks (`) or double quotes ("), however, I'd go with Fahim Parkar's comment above and just rename the table.

Also worth noting, you must use ANSI_QUOTES SQL mode if using double quotes per Section 9.2:

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks

edited Jun 12 '12 at 21:18

answered Jun 12 '12 at 21:04

xelco52

5,257
4
40
56

1

Finally I have got working query. I dont know why the others solutions werent working. But this works : `serialtracker.\`show\`` – zdarsky.peter Jun 12 '12 at 21:42

score 4 · Answer 2 · answered Nov 15 '12 at 07:19

4

The problem is with YEAR not with SHOW. YEAR is a MySQL function. Best practice is to quote column and tables names all the time, makes things easy to read also.

Should be:

SELECT SQL_CALC_FOUND_ROWS `year`, `nameShow` 
FROM   `show`
LIMIT 0, 10

answered Nov 15 '12 at 07:19

Alex S

147
2
3

1

MySQL does not restrict function names from being used in aliases. Only _reserved keywords_ are restricted in aliases. See https://dev.mysql.com/doc/refman/8.0/en/keywords.html (only the `(R)` ones). (also not restricted in 5.5, 5.6 and 5.7) – SOFe Aug 25 '18 at 09:42

score 1 · Answer 3 · answered Jun 12 '12 at 21:16

1

Backticks should work fine

try putting a comma after SQL_CALC_FOUND_ROWS,

SELECT SQL_CALC_FOUND_ROWS, year, nameShow 
FROM   `show`
LIMIT 0, 10

answered Jun 12 '12 at 21:16

bumperbox

10,166
6
43
66

I know it should. But I dont know what am I doing wrong. `$sQuery = " SELECT SQL_CALC_FOUND_ROWS year, nameShow FROM `show` $sWhere $sOrder $sLimit ";` – zdarsky.peter Jun 12 '12 at 21:18
1

@zdarsky.peter : Could you post your query in your question?? In above query what is $s after `show`?? I think that is your problem – Fahim Parkar Jun 12 '12 at 21:21

Escape table name MySQL

3 Answers3

Linked