Handling MySQL Backticks When Switching DB engines using PHP's PDO Interface

Question

1. I'm currently working on a PHP application that uses a MySQL database for its backend
2. All of my queries contain backticks to escape the field names. This is so I can have fields like "password" in a query without causing issues (see example)
3. I know that backticks are not universal between relational-database engines (SQLite uses a double-quote, for example)
4. All of the queries in my php application are executed using PHP's PDO interface

My question is this: If I want to switch database engines, say from MySQL to SQLite, what do I need to do to handle the backticks in all of my queries? I really don't want to have to go through all of my code and change / remove the backticks. Any suggestions? Am I doing something wrong or not within the boundaries of best practices?

Sample Query:

SELECT
   `username`,
   `password`,
   `email_address`
FROM
   `users`
WHERE
   `id` = '1'

Answer 1

Don't use reserved words and you don't get into trouble when you don't use backticks. Get rid of all backticks, it's not SQL Standard, all other databases will have problems with them. Double quotes are used in the standard, most databases support them. But again, don't use reseved words and you don't need them.

Configure your MySQL-server (-connection) to use ANSI-QUOTES and MySQL will also treat double quotes as it should have done in the first place: as an identifier

Answer 2

Actually, password does not really need to be quoted... It's not even a reserved word: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

IMHO, the best approach you can take is:

1. Do not use reserved words in your identifiers.
2. Remove quotes from current code; it's a 2 minute task with any decent editor (unless you're also using the backtick operator)

Whatever, switching to another DB engine is one thing; building a DB-independent app is a enterely different issue.

Answer 3

Actually, SQLite is compatible with MySQL backtick quoting style.

The inverse however, is only true if you follow @Frank advice.