• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • PDO::quote()

    (PHP 5 >= 5.1.0, PHP 7, PECL pdo >= 0.2.1)

    为 SQL 查询里的字符串添加引号

    说明

    publicPDO::quote(string $string[,int $parameter_type= PDO::PARAM_STR]): string

    PDO::quote()为输入的字符串添加引号(如果有需要),并对特殊字符进行转义,且引号的风格和底层驱动适配。

    如果使用此函数构建 SQL 语句,强烈建议使用PDO::prepare()配合参数构建,而不是用PDO::quote()把用户输入的数据拼接进 SQL 语句。使用 prepare 语句处理参数,不仅仅可移植性更好,而且更方便、免疫 SQL 注入;相对于拼接 SQL 更快,客户端和服务器都能缓存编译后的 SQL 查询。

    不是所有的 PDO 驱动都实现了此功能(例如 PDO_ODBC)。考虑使用 prepare 代替。

    Caution

    安全性:默认字符集

    字符集不仅仅要在数据库服务器上设置,也要为数据库连接设置(取决于驱动),它影响了PDO::quote()。更多信息可参考PDO 驱动文档。

    参数

    $string

    要添加引号的字符串。

    $parameter_type

    为驱动提示数据类型,以便选择引号风格。

    返回值

    返回加引号的字符串,理论上可以安全用于 SQL 语句。如果驱动不支持这种方式,将返回FALSE

    范例

    普通字符串加引号

    <?php
    $conn = new PDO('sqlite:/home/lynn/music.sql3');
    /* 简单字符串 */
    $string = 'Nice';
    print "Unquoted string: $string\n";
    print "Quoted string: " . $conn->quote($string) . "\n";
    ?>
    

    以上例程会输出:

    Unquoted string: Nice
    Quoted string: 'Nice'
    

    危险字符串加引号

    <?php
    $conn = new PDO('sqlite:/home/lynn/music.sql3');
    /* 危险字符串 */
    $string = 'Naughty \' string';
    print "Unquoted string: $string\n";
    print "Quoted string:" . $conn->quote($string) . "\n";
    ?>
    

    以上例程会输出:

    Unquoted string: Naughty ' string
    Quoted string: 'Naughty '' string'
    

    复杂字符串加引号

    <?php
    $conn = new PDO('sqlite:/home/lynn/music.sql3');
    /* 复杂字符串 */
    $string = "Co'mpl''ex \"st'\"ring";
    print "Unquoted string: $string\n";
    print "Quoted string: " . $conn->quote($string) . "\n";
    ?>
    

    以上例程会输出:

    Unquoted string: Co'mpl''ex "st'"ring
    Quoted string: 'Co''mpl''''ex "st''"ring'
    

    参见

    • PDO::prepare() 准备要执行的语句,并返回语句对象
    • PDOStatement::execute() 执行一条预处理语句
    When converting from the old mysql_ functions to PDO, note that the quote function isn't exactly the same as the old mysql_real_escape_string function. It escapes, but also adds quotes; hence the name I guess :-)
    After I replaced mysql_real_escape_string with $pdo->quote, it took me a bit to figure out why my strings were turning up in results with quotes around them. I felt like a fool when I realized all I needed to do was change ...\"".$pdo->quote($foo)."\"... to ...".$pdo->quote($foo)."...
    PDO quote (tested with mysql and mariadb 10.3) is extremely slow.
    It took me hours of debugging my performance issues until I found that pdo->quote is the problem.
    This function is far from fast, and it's PHP instead of C code:
    function escape($value)
      {
        $search = array("\\", "\x00", "\n", "\r", "'", '"', "\x1a");
        $replace = array("\\\\","\\0","\\n", "\\r", "\'", '\"', "\\Z");
        return str_replace($search, $replace, $value);
      }
    It is 50 times faster than pdo->quote()
    (note, it's without quotes just escaping and only used here as an example)
    One have to understand that string formatting has nothing to do with identifiers.
    And thus string formatting should NEVER ever be used to format an identifier ( table of field name).
    To quote an identifier, you have to format it as identifier, not as string.
    To do so you have to
    - Enclose identifier in backticks.
    - Escape backticks inside by doubling them.
    So, the code would be:
    <?php
    function quoteIdent($field) {
      return "`".str_replace("`","``",$field)."`";
    }
    ?>
    this will make your identifier properly formatted and thus invulnerable to injection. 
    However, there is another possible attack vector - using dynamical identifiers in the query may give an outsider control over fields the aren't allowed to:
    Say, a field user_role in the users table and a dynamically built INSERT query based on a $_POST array may allow a privilege escalation with easily forged $_POST array. 
    Or a select query which let a user to choose fields to display may reveal some sensitive information to attacker.
    To prevent this kind of attack yet keep queries dynamic, one ought to use WHITELISTING approach.
    Every dynamical identifier have to be checked against a hardcoded whitelist like this:
    <?php
    $allowed = array("name","price","qty");
    $key = array_search($_GET['field'], $allowed));
    if ($key == false) {
      throw new Exception('Wrong field name');
    }
    $field = $db->quoteIdent($allowed[$key]);
    $query = "SELECT $field FROM t"; //value is safe
    ?>
    (Personally I wouldn't use a query like this, but that's just an example of using a dynamical identifier in the query).
    And similar approach have to be used when filtering dynamical arrays for insert and update:
    <?php
    function filterArray($input,$allowed)
    {
      foreach(array_keys($input) as $key )
      {
        if ( !in_array($key,$allowed) )
        {
           unset($input[$key]);
        }
      }
      return $input;
    }
    //used like this
    $allowed = array('title','url','body','rating','term','type');
    $data = $db->filterArray($_POST,$allowed); 
    // $data now contains allowed fields only 
    // and can be used to create INSERT or UPDATE query dynamically
    ?>
    
    This function also converts new lines to \r\n
    Note that this function just does what the documentation says: It escapes special characters in strings. 
    It does NOT - however - detect a "NULL" value. If the value you try to quote is "NULL" it will return the same value as when you process an empty string (-> ''), not the text "NULL".
    In foundation quoting is bad idea,there always will be possibillity to escape or fraud quote function ,better solution,i mean best solution is using this function : htmlentities($string, ENT_QUOTES, 'UTF-8') which translate quote into &#39; and $string translated like this can't affect on your code.

    上篇:PDO::query()

    下篇:PDO::rollBack()