Difference between ` and ' in php-mysql queries

PHP
1

Hi,

is there any Difference between ` and ’ in php-mysql queries?

2

Mysql uses backticks ( ` ) for quoting the table and column names in SQL queries.

Mysql can use either ’ or " for quoting strings inside SQL queries.

So to update name with Joe you could end up with a PHP string, which contains a legitimate SQL string which looks like:


$sql = "update `my_table` set `name` = 'Joe' where id = 34";

But equally this would be allowed:


$sql = "update my_table set name = 'Joe' where id = 34";

The backticks are not necessary unless you name your table or one of your columns using one of the Mysql reserved words.

So if you had to update a table containing a column called before you MUST backtick ( ` ) quote it because before is on that list.


$sql = "update my_table set `before` = '2012-02-01' where id = 34";

A couple of other things to note in those examples:

Date and time fields must be quoted like strings.
Number fields in your database such as an auto-increment id field do NOT need quoting
You could reverse the single and double quoting and it would still work

Short version: backticks are for mysql sql statements. Single/double quotes must deliminate strings in both PHP and Mysql so you must carefully respect your use of them.

3

As a completion, to avoid SQL-injection you may

 <?php
// if colname should be an INTeger (int, bigint, tyniint etc)
$var = (int)$var;
$query = 'SELECT stuff FROM `table` WHERE colname = ' . $var;

OR

 <?php
// if colname is a string or date
$query = 'SELECT stuff FROM `table` WHERE colname = "'.escape( $var ).'"';
// where you will have
function escape($s) {
    $s = stripslashes($s);
    return mysql_real_escape_string($s);
}

OR, if you are using PDO, you will not have to worry about this

 <?php
// just use statements
// do PDO connection...
// ... check http://www.php.net/manual/en/pdostatement.execute.php for an example
$query = 'SELECT stuff FROM `table` WHERE colname = :col ';
$PDOStatement = $PDOobj->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$result = $PDOStatement->execute( array( ':col' => $var ) );
4

Thank you people. That was pretty helpful.

What is this PDO? I did lookup on php.net, I’m not sure I understood that. What is the goal of it.

5

PDO (PHP Data Objects) is an abstraction layer for database connectors.

Basically, instead of having to write a bunch of files to define methods for each possible connection type, a PDO object can be used (mostly) flexibly.