我的PDO查询是否可以安全地从SQL注入[重复]

This question already has an answer here:

这个问题在这里已有答案：

Are PDO prepared statements sufficient to prevent SQL injection? 7 answers
PDO准备好的语句是否足以阻止SQL注入？ 7个答案

I'm fairly new to PDO and wondering if my query below is safe from SQL injection. I'll be using this method throughout the site if so.

我是PDO的新手，想知道我的查询是否可以安全地从SQL注入。如果是这样的话，我会在整个网站上使用这种方法。

    // make connection to DB
$db = new PDO('mysql:host='.$dateBaseHost.';dbname='.$dateBaseName, $dateBaseUsername, $dateBasePassword);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


//simple query and binding with results
$query = $db->prepare(" SELECT * FROM `profile` WHERE `fullname` = :fullname ");

$search = (isset($_GET['search']) === true) ? $_GET['search'] : '' ; // ? : shorthand for if else

// bind parameters - avoids SQL injection
$query->bindValue(':fullname', $search);

//try... if not catch exception
try {
    // run the query
    $query->execute();

    $rows = $query->fetchAll(PDO::FETCH_ASSOC);
    echo '<pre>', print_r($rows, true),'</pre>';
}
catch (PDOException $e){
    sendErrorMail($e->getMessage(), $e->getFile(), $e->getLine());
}

4 个解决方案

#1

Yes - parameterized queries are safe from injection when used in this way.

是 - 当以这种方式使用时，参数化查询可以安全地进行注入。

#2

As long as you use prepared statements properly, you're safe from injection. but as soon as you DIRECTLY insert any external data into a query, even if it's otherwise a prepared statement, e.g.

只要你正确使用准备好的陈述，你就可以安全地注射。但是一旦你直接将任何外部数据插入到查询中，即使它是一个准备好的语句，例如

INSERT INTO $table VALUES (:param)

you're vulnerable - $table can be subverted in this case, even though you're using a prepared statement.

你很脆弱 - 在这种情况下，$ table可以被破坏，即使你正在使用预备语句。

Anyone who tells you simply switching mysql->PDO or mysqli will make you safer is a flat out WRONG. You can be just as vulnerable to injection attacks with either library.

任何告诉你只需切换mysql-> PDO或mysqli的人都会让你更安全。你可以像使用任何一个库一样容易受到注入攻击。

#3

You should also

你也应该

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

By default it uses emulated mode, which merely does what mysql_real_escape_string does. In some edge cases, you're still vulnerable to SQL injection.

默认情况下，它使用模拟模式，它只执行mysql_real_escape_string所做的操作。在某些边缘情况下，您仍然容易受到SQL注入攻击。

#4

yes, it's fairly safe but whole script could be improved:

是的，这是相当安全的，但整个脚本可以改进：

if (isset($_GET['search']) {
    // make connection to DB
    $opt = array(
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );
    $dsn = "mysql:host=$dateBaseHost;dbname=$dateBaseName;charset=$dateBaseCharset";
    $db  = new PDO($dsn, $dateBaseUsername, $dateBasePassword, $opt);

    //simple query and binding with results
    $query = $db->prepare("SELECT * FROM profile WHERE fullname = ?");
    $query->execute(array($_GET['search']));
    $rows = $query->fetchAll();
    echo '<pre>', print_r($rows, true),'</pre>';
}

you need to set errmode as a connection option
您需要将errmode设置为连接选项
never use try..catch to handle error message. if you want to have a email on every error (which is just crazy), you have to set up my_exception handler() for this.
永远不要使用try..catch来处理错误消息。如果你想在每个错误上都有一封电子邮件（这很疯狂），你必须为此设置my_exception handler（）。
setting search to empty string doesn't make any sense
将搜索设置为空字符串没有任何意义
connect to PDO should be moved so separate file (not shown)
连接到PDO应移动到如此单独的文件（未显示）
charset have to be set in DSN
必须在DSN中设置charset

#1