mysql double-quoted table names

mysql double-quotes
Mohamed Wagdy Khorshid picture Mohamed Wagdy Khorshid · Dec 14, 2012 · Viewed 8.9k times · Source

I'm doing a mysql query like:

Select * from "User";

and it returns:

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 '"User"' at line 1

The error has something to do with the double-quotes ", can I keep the select statement as is, and make mysql cope with the double quotes?

Answer

Wesley Murch picture Wesley Murch · Dec 14, 2012

Taken from this post:

SET GLOBAL SQL_MODE=ANSI_QUOTES;

Personally when I tested, I had to do it like this:

SET SQL_MODE=ANSI_QUOTES;

I don't think there's any other way.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes

ANSI_QUOTES

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More