SQL QUESTION - Difference between using special characters with '' or with "" in a query in PHP

Question

In this php function I'm doing a query to tables that the name contain special characters like $ so in php If I use "" in the query the application thinks that the table name is a variable so it returns Variable not found.

Exemple 1:

$SQL = "SELECT COUNT(*)
        FROM [table_name$1] 
        left join [table_name$2] as d on [Code] = d.[Code]
        where d.[Dimension Code] = 'NAT'
        and [Request Code] not like 'AC%'";

But if I use '' in the query he thinks that everything inside "" is a column so it returns Invalide column Name in this case NAT

Exemple 2:

$SQL = 'SELECT COUNT(*)
        FROM [table_name$1] 
        left join [table_name$2] as d on [Code] = d.[Code]
        where d.[Dimension Code] = "NATUREZA FUNC"
        and [Request Code] not like "AC%"';

Best way to escape this problem? I now you can use mysql_real_escape_string to escape them but there´s a lot of tables. I would like to now if it exists a more efficient way

Answer 1

That is a really unclear database naming convention. Have you tried to use backticks ( ` ) to surround table references. See: When to use single quotes, double quotes, and backticks in MySQL

Also using prepared statements might be a better option.

Answer 2

The solution to this problem was using prepared statements.

$SQL = 'SELECT COUNT(*)
        FROM [table_name$1] 
        left join [table_name$2] as d on [Code] = d.[Code]
        where d.[Dimension Code] = :nat
        and [Request Code] not like :ac';

And then use bind value

$result->bindValue(':nat', "nat, PDO::PARAM_STR);
$result->bindValue(':year', $ac, PDO::PARAM_STR);