Quoting SQL in Magento

Well, most of new Magento developers feel that writing queries again database is quite complicated. And so, they try to use the simplest way to do this:

$data = Mage::getSingleton(‘core/resource’)  ->getConnection(‘core_read’)->query($sql);

However, sometimes you may make Mysql injection error by this way. To avoid this, the best way is using quoting sql supported by Zend Db before calling query().

There are 3 kinds of quoting: quote(), quoteInto() and quoteIdentifier(). It’s very important to use these functions before execute query. It helps us to defend against SQL injection issues.

1. quote(): this method is used to turn a string or variable into a quoted SQL string:

$where = $db->quote("April's coder");
$db->query("Select * from `coders` where `award`=$where");

In the first command, we will have $where = ‘April\’s coder’; notice that 2 quotes added to the string.

2. quoteInto(): used to substitute a scalar into a SQL expression

If you have an expression like this: Select * from `coders` where `award`=’April’s coder’; you should write:

$whereExpr = $db->quoteInto('award=?', "April's coder");

$db->query("Select * from `coders` where $whereExpr");

Or another way:

$query = $db->quoteInto("Select * from `coders` where `award`=?", "April's coder");
$db->query($query);

3. quoteIdentifier(): Identifiers can be table names, columns name,… You should use quoteIdentifier() method if your identifiers contain white space, special characters, SQL keywords or is case-sensitive,…

$tablename = $db->quoteIdentifier('My Table');
$db->query("Select * from $table where ...");

Maybe this is not a good tutorial, but I am sure it’s useful for someone just started with Zend_Db. I hope that I can convey a new Magento database concept after this article.