在使用PDO时适当转义字段和查询设置 [英] Properly escaping fields and query settings when using PDO

问题描述

可能重复：

我使用PDO，因为它被推荐为PHP数据库连接的方式。但是同时，我很害怕确保我的查询，以确保我的系统是尽可能安全的黑客。

PDO和准备语句是一个伟大的方式去，但我有几个问题。我有一个自定义过滤系统，要求我手动构建一个查询。例如：

  $ query = $ pdo-> prepare（'SELECT * FROM log WHERE username =？'） ; 
 $ result = $ query-> execute（array（$ _ GET ['username']））; 
  

这个工作和一切都很棒 - PDO处理确保$ _GET变量不会损害我的查询。

但是当我需要逃避其他事情时该怎么办？例如，如果我有这种情况，我想只返回五个记录：

  $ query = $ pdo-> prepare（'SELECT * FROM log WHERE username =？LIMIT 5'）; 
 $ result = $ query-> execute（array（$ _ GET ['username']））; 
  

但是如果限制值也来自$ _GET呢？如何逃避它？

为了做到这一点，我首先想要手动构建查询并使用PDO :: quote（）方法，如下所示：

  $ query ='SELECT * FROM log WHERE username =？ LIMIT'。$ pdo-> quote（$ _ GET ['limit']）; 
  

但这没有效果，因为它在限制符>

有没有正确的方式使用PDO转义mysql_real_escape_string（）的工作方式？因为后者从来没有在引起变量的引号，但我不能停止这种行为与quote（）。

另一种方法是构建自己的escaper，但这种类型的打败了使用PDO准备的语句开始的目的（准备语句本身总是在值​​周围加引号）。

编辑：我也试过将值转换为整数，如下：

  $ pdo-> quote（（int）$ value，PDO :: PARAM_INT）; 
  

但它 - 与intval（）相同。

为什么PDO这么积极建议和推荐使用，如果我还要做那些原始的东西，我真的不想为这样的情况写一个消毒方法，希望没有任何破坏或被破坏。

解决方案

关注整数值。由于 $ _ GET 始终是字符串，因此可以将其转换为带有转换的整数，或％d code> sprintf ：

  $ query = $ pdo-> prepare $ b'SELECT * FROM log WHERE username =？LIMIT'。（int）$ _GET ['page'] 
）; 
 
 $ query $ pdo-> prepare（
 sprintf（'SELECT * FROM log WHERE username =？LIMIT％d'，$ _GET ['page']）
 ）; 
  

如果你确实需要一个字符串， quote（）函数是合适的。

Possible Duplicate:
How do I use pdo's prepared statement for order by and limit clauses?

I'm using PDO as it has been recommended as the way to go when it comes to PHP database connections. But at the same time I am a obsessed with securing my queries to make sure that my system is as safe as possible from hackers.

PDO and prepared statements is a great way to go, but I have a couple of issues with it. I have a custom filtering system that demands I build a query manually. For example, this:

$query=$pdo->prepare('SELECT * FROM log WHERE username=?');
$result=$query->execute(array($_GET['username']));

This works and everything is great - PDO deals with making sure the $_GET variable does not harm my query.

But what to do when I need to escape other things? For example, if I have this situation, where I want to return just five records:

$query=$pdo->prepare('SELECT * FROM log WHERE username=? LIMIT 5');
$result=$query->execute(array($_GET['username']));

This, again, works. But what if the limit values also come from $_GET? How to escape it?

To do this I first figured that I have to build the query manually and use PDO::quote() method, like this:

$query='SELECT * FROM log WHERE username=? LIMIT '.$pdo->quote($_GET['limit']);

But this did not work, since it placed quotes around the limiter which breaks the query.

Is there a proper way of escaping with PDO the way mysql_real_escape_string() worked? Since the latter never put quotes around the resulting variable, but I'm unable to stop this behavior with quote().

Alternative would be to build my own escaper, but that kind-of defeats the purpose of using PDO prepared statements to begin with (prepared statements themselves always put quotes around values).

EDIT: I also tried casting the value as integer in quote, like this:

$pdo->quote((int)$value,PDO::PARAM_INT);

But it -still- places quotes around it. Same with intval().

Why is PDO so actively suggested and recommended for use if I have to do even primitive things like that custom? I really don't want to write a sanitizing method for cases like this and hope that nothing breaks or is compromised.

解决方案

You are concerned about integer values. As $_GET is always string, you can turn it into an integer with a cast or the %d format of sprintf:

$query = $pdo->prepare(
    'SELECT * FROM log WHERE username=? LIMIT ' . (int) $_GET['page']
);

$query = $pdo->prepare(
    sprintf('SELECT * FROM log WHERE username=? LIMIT %d', $_GET['page'])
);

If you actually need a string, the quote() function you already wrote about is appropriate.

这篇关于在使用PDO时适当转义字段和查询设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！