here are the quotes i will be refering to in this post:
’ single quote, a.k.a. apostrophe
" double quote
` backtick (on the same key as ~ on US keyboards)
let’s start by discussing ANSI SQL standard quote usage. in ANSI SQL, single quotes (and only single quotes) are used to signify a string. if a string contains a single quote, double the quote. for purposes of correctly processing a query, this is the ONLY escaping that needs to be done and is completely supported by mysql. here’s an example:
insert
into users
( username, first, last, active )
values
( 'brian', 'Brian', 'O''Reilly', 0 )
, ( 'billy', 'Billy', 'O''Toole', 1 )
note that the last names contain quotes and therefore have the single quotes doubled. when mysql stores the data, the extra single quotes will be removed. therefore, there’s no need to try and remove doubled single quotes when retrieving data.
best practice: escape quotes only by doubling the single quotes. since it’s ANSI SQL standard, it work with most other database servers. even though mysql supports escaping by using backslashes, this is not always portable to other servers. since doubling the single quotes is ANSI SQL standard, it’s very likely to be portable.
however, if you adopt a database abstraction layer for database connectivity and it uses slashes for escaping, make sure the only function from the mysql client library it uses to insert those slashes is mysql_real_escape_string() as it is the only function guaranteed to be appropriate for mysql. (by extension, yes, PHP’s addslashes() and magic_quotes are NOT appropriate)
notice also that the numbers were not in quotes. ANSI SQL does not allow numbers to be in quotes.
best practice: for maximum portability, do not put numbers in quotes.
next up is the double quote. in ANSI SQL, double quotes are used to quote identifiers (database, table and column names). use them in cases where the names of your objects contain spaces, operators (. , + / etc.) or are reserved words. here’s an example:
select id
, "product name"
, "count"
from inventory
where size = 'XL'
unfortunately, by default mysql does not allow double quotes to be used for identifiers. by default, double quotes in mysql can also be used to identify strings, just like single quotes. this can be changed by setting the SQL mode, but it is unusual in mysql installations to see this. mysql instead uses backticks to quote identifiers. unfortunately, this backtick syntax is not widely supported by other servers.
best practice: use names that do not requires quoting. this is the only thing guaranteed to be supported by servers from different vendors since it’s ANSI SQL compatible. staying away from reserved words and limiting yourself to letters, numbers and underscores is a good way to stay safe.
bonus tip: DO NOT use microsoft word for ANYTHING relating to SQL development. microsoft word has this nasty habit of converting single and double quotes to ‘ ’ and “ ”, which are not recognized by SQL, mysql or otherwise. do yourself a favor and get a real code editor.
references:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html