Escape special characters

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

Components used in this example

• Zend_Db
• Zend_Db_Adapter_Pdo_Sqlite

---

MyDbQuote MyHtml Entry point HTML | Try it | View source code

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);
    }

}

Displaying items

class MyHtml
{

Displaying the title of the page based on the file name.

    public static function printTitle()
    {
        $basename = basename(__FILE__, '.php');
        $title = ucwords(str_replace('-' , ' ', $basename));
        $zfVersion = Zend_Version::VERSION;
        $phpVersion = phpversion();
        echo "ZfEx $title (ZF/$zfVersion PHP/$phpVersion)";
    }

Displaying the selected option

    public static function printSelected($value, $target)
    {
        $value == $target and print 'selected="selected"';
    }

Displaying a string or an array

• If the data is an array, we convert the array into a set of lines.
• We converts special characters into HTML entities.
• We convert new-line characters into line breaks.

    public static function display($mixed)
    {
        // If the data is an array, we convert the array into a set of lines.
        is_array($mixed) and $mixed = implode("\n", $mixed);
        $mixed = stripslashes($mixed);
        // We converts special characters into HTML entities.
        $mixed = htmlspecialchars($mixed, ENT_QUOTES, 'UTF-8');
        // We convert new-line characters into line breaks.
        echo nl2br($mixed);
    }

}

Processing the query

• We get all the parameters and the SQL statement, to display in the form.

$dbQuote = new MyDbQuote;
// We get all the parameters and the SQL statement, to display in the form.
list($quote, $value, $type, $column, $sql) = $dbQuote->process();

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title><?php MyHtml::printTitle();?></title>
    <style type="text/css">
      body, td {
          font-family: arial, sans-serif;
          font-size: 0.9em;
      }
    </style>
  </head>

  <body>

    <p>EXAMPLE <?php MyHtml::printTitle();?></p>
    <hr />

    <form>
      <table>
        <tr>
          <td>Value</td>
          <td><input type="text" name="value" value="<?php MyHtml::display($value);?>" /></td>
          <td>Examples: 123 or bla bla or it's cool!</td>
        </tr>

        <tr>
          <td>Type</td>
          <td>
            <select name="type">
              <option></option>
              <optgroup label="SQLite">
              <option <?php MyHtml::printSelected($type, 'INTEGER');?>>INTEGER</option>
              <option <?php MyHtml::printSelected($type, 'REAL');?>>REAL</option>
              <option <?php MyHtml::printSelected($type, 'NUMERIC');?>>NUMERIC</option>
              <option <?php MyHtml::printSelected($type, 'TEXT');?>>TEXT</option>
              <option <?php MyHtml::printSelected($type, 'NONE');?>>NONE</option>
              </optgroup>
              <optgroup label="ZEND_Db">
              <option <?php MyHtml::printSelected($type, 'BIGINT_TYPE');?>>BIGINT_TYPE</option>
              <option <?php MyHtml::printSelected($type, 'INT_TYPE');?>>INT_TYPE</option>
              <option <?php MyHtml::printSelected($type, 'FLOAT_TYPE');?>>FLOAT_TYPE</option>
              </optgroup>
            </select>
          </td>
        </tr>

        <tr>
          <td>Column</td>
          <td><input type="text" name="column" value="<?php MyHtml::display($column);?>" /></td>
          <td>Examples: table or "bla bla"</td>
        </tr>

        <tr>
          <td>Quote method</td>
          <td>
            <select name="quote" value="<?php echo $quote;?>">
              <option></option>
              <optgroup label="Quote value">
                <option <?php MyHtml::printSelected($quote, 'quote');?>>quote</option>
                <option <?php MyHtml::printSelected($quote, 'quoteInto');?>>quoteInto</option>
              </optgroup>
              <optgroup label="Quote column">
                <option <?php MyHtml::printSelected($quote, 'quoteIdentifier');?>>quoteIdentifier</option>
              </optgroup>
            </select>
          </td>
        </tr>

        <tr>
          <td></td>
          <td><input type="submit" value="Submit" /></td>
        </tr>
      </table>
    </form>

    <hr />
    QUOTING RESULT
    <br /> <br />
    <?php MyHtml::display($sql);?>

  </body>
</html>