Thursday, December 3, 2009

Escape special characters

In this example, we process values and identifiers in a SQL statement.

Components used in this example
Implementation of the methods to escape special characters.
  • We define the set of the available value types.

class MyDbQuote
// We define the set of the available value types.
private $_quoteTypes = array(
null => null,
'REAL' => 'REAL',
'TEXT' => 'TEXT',
'NONE' => 'NONE',
'INT_TYPE' => Zend_Db::INT_TYPE,
Processing the query
  • We get the method, the value, the type and the column from the GET request.
  • We instantiate a database object.
  • We call the method to escape the special characters in the SQL statement.
  • We return the corrected SQL statement.

    public function process()
// We get the method, the value, the type and the column from the GET request.
list($quote$value$type$column) = $this->_getParameters();
// We instantiate a database object.
$db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));

// We call the method to escape the special characters in the SQL statement.
switch ($quote) {
'quote' :
$quoted $db->quote($value$this->_quoteTypes[$type]);
$sql "SELECT * FROM my_table WHERE $column = $quoted";

'quoteInto' :
$sql $db->quoteInto("SELECT * FROM my_table WHERE $column = ?",

'quoteIdentifier' :
$quoted $db->quoteIdentifier($column);
$sql "SELECT * FROM my_table WHERE $quoted = $value";

$sql "SELECT * FROM my_table WHERE $column = $value";

// We return the corrected SQL statement.
return array($quote$value$type$column$sql);
Extraction of the parameters from the GET request

    private function _getParameters()
$quote = isset($_GET['quote'])? $_GET['quote'] : null;
$value = isset($_GET['value'])? $_GET['value'] : 123;
$column = isset($_GET['column'])? $_GET['column'] : 'my_column';

$_GET['type']) and $type $_GET['type'] and
$this->_quoteTypes[$type]) or $type null;

        return array(


No comments:

Post a Comment