Double quotes vs. single quotes

nogeekyet

I usually create queries this way:

$query = "SELECT *
FROM students
WHERE S_DMVSchoolNbr = '$SchoolNbr'
AND S_StudentLogin = '$LoginID'
";

However, when it comes to names I swap the quotes around because of names such as O'Neil:

$query = 'SELECT *
FROM students
WHERE S_DMVSchoolNbr = "$SchoolNbr"
AND S_StudentLogin = "$LoginID"
';
This seems to work fine until I need to do a DELETE. If I put the entire query into double quotes, it work. If I put the query into single quotes and the variables into double quotes, it does not work.:bemused:

dagon

you should be sanitising your input with the likes of: mysql_real_escape_string this will take care of quoted content

nogeekyet

OK, I will try that. Thanks a lot.

laserlight

Incidentally, you might want to know that single quotes are standard here. Using double quotes to quote strings in SQL is non-standard; in fact double quotes are used to quote identifier names.

Ashley_Sheridan

Slashes are used to escape single quotes in SQL statements, i.e.
"SELECT * FROM table WHERE name = 'O\'neil'"

There are proper functions for this sort of thing, that also help protect against SQL injection attacks. mysql_escape_string() and mysql_real_escape_string() (the second one needs an active database connection to be declared to work). Other database types have similar functions for this sort of thing as well.

laserlight

Ashley Sheridan wrote:
Slashes are used to escape single quotes in SQL statements, i.e.
"SELECT FROM table WHERE name = 'O\'neil'"

On the contrary, single quotes are used to escape single quotes in SQL statements, i.e.,

"SELECT * FROM tablename WHERE name = 'O''neil'"

But some database systems, including MySQL, allow the use of a prepended backslash.

Besides what dagon and Ashley Sheridan have mentioned about using certain functions to sanitise your data, I suggest that you use prepared statements, e.g., with the PDO extension.