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.