MySql table names and field names within quotations.

Question

I have a bunch of PHP code that is auto-generated and it puts all table names and field names within quotes. To wit:

INSERT INTO "t_ML_u_Visitor"("c_u_ID") VALUES ...

Since the code is Auto-Generated it would be much preferred if I could somehow get MySql to accept this syntax rather than subjecting the generated code to a post-process, that strips the quotes.

I have tried this:

$conn=$mysqli = new mysqli("localhost", "root", "", "myDB");
$R=$conn->options(MYSQLI_INIT_COMMAND, "SET SQL_MODE = 'ANSI'"); 

But to no avail.

Help?

Answer 1

The normal quote character is the back-quote - eg

create table `quoted`( `v` varchar(10) );

Can you change the quote character your auto-generated queries use to suit ?

EDIT: I just tried ANSI mode, and it does what you want:

set @session sql_mode = ANSI;
create table "world"( "v" varchar(10));

The problem then becomes that double-quote can no longer be used to quote literal strings.

Answer 2

MySQL uses backticks (`) to escape. Your query then looks like this:

INSERT INTO `t_ML_u_Visitor` (`c_u_ID`) VALUES (...)

If you are using PHP and Mysqli (as I see) you can use the function real_escape_string() of mysqli like this:

$conn = new mysqli("localhost", "root", "", "myDB");
$query = "INSERT INTO t_ML_u_Visitor (c_u_ID) VALUES (...)";
$escaped = $conn->real_escape_string($query);
$result = $conn->query($escaped);

See also: http://de2.php.net/manual/en/mysqli.real-escape-string.php