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,
        
'INTEGER' => 'INTEGER',
        
'REAL' => 'REAL',
        
'NUMERIC' => 'NUMERIC',
        
'TEXT' => 'TEXT',
        
'NONE' => 'NONE',
        
'BIGINT_TYPE' => Zend_Db::BIGINT_TYPE,
        
'INT_TYPE' => Zend_Db::INT_TYPE,
        
'FLOAT_TYPE ' => Zend_Db::FLOAT_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) {
            case 
'quote' :
                
$quoted $db->quote($value$this->_quoteTypes[$type]);
                
$sql "SELECT * FROM my_table WHERE $column = $quoted";
                break;

            case 
'quoteInto' :
                
$sql $db->quoteInto("SELECT * FROM my_table WHERE $column = ?",
                    
$value$type);
                break;

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

            default:
                
$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';

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

        return array(
$quote$value$type$column);
    }

}

No comments:

Post a Comment