real_escape_string和prepare()之间的区别?

问题描述

prepare()对于我的大多数代码而言似乎有些乏味且不必要.如果我在SQL命令中发送字符串，为什么不能仅用real_escape_string对其进行清理?有什么不同?这就是我一直在做的事情，它在SQL注入方面效果很好...谢谢.

prepare() seems a bit tedious and unnecessary for a majority of my code. If I send a string in a SQL command, why can't I just sanitize it with real_escape_string? What's the difference? That's what I've been doing all the time and it worked well against SQL injections... Thanks.

推荐答案

转义在SQL注入防御中与使用查询参数一样有效.

Escaping is just as effective at SQL injection defense as using query parameters.

如果您未能始终如一地执行这两种方法，它们的效率也将降低.

Both methods are also less effective if you fail to do them consistently.

这两种方法仅用于保护SQL表达式中的各个值.它们不支持查询的其他动态部分.例如，如果要对用户指定的列进行ORDER BY.查询参数和转义函数都无法处理.

Both methods are useful only for protecting individual values in SQL expressions. They don't support other dynamic parts of the query. For example, if you want to ORDER BY a user-specified column. Neither query parameters nor escaping functions handle that.

因此，基本上，这是样式和个人喜好问题.

So basically, it is a matter of style and personal preference.

我更喜欢查询参数，因为我认为:

I prefer query parameters because I think this:

$sql = "INSERT INTO mytable (columna, columnb, columnc) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$a, $b, $c]);

比这更清楚:

$sql = "INSERT INTO mytable (columna, columnb, columnc) VALUES ('".mysqli_real_escape_string($conn, $a)."', '".mysqli_real_escape_string($conn, $b)."', '".mysqli_real_escape_string($conn, $c)."')";
mysqli_query($conn, $sql);

不能严肃地说，摆弄所有这些打开引号/关闭引号和.字符串连接要比使用带有查询参数的prepare()容易.

You can't seriously be saying that fiddling with all those open-quotes/close-quotes and . string concatenation is easier than using prepare() with query parameters.

用参数对假设的query()函数发表评论.

Re your comments about a hypothetical query() function with parameters.

首先，没有必要.一起使用prepare()和execute()可以花很少的钱来编写安全的代码，并且通过坚持使用单个函数来做到这一点，您听起来很懒.我想您也不检查错误返回false的函数的返回值吗?

First of all, it's not necessary. Using prepare() and execute() together is a small price to pay for writing secure code, and by insisting on doing it with a single function, you just sound lazy. I suppose you don't check the return value of functions that return false on error, either?

对于它的价值，编写一个包装函数可以同时实现这两个功能很容易，因为PHP隐式支持varargs.

For what it's worth, it'd be easy to write a wrapper function to do both, because PHP supports varargs implicitly.

function myquery() {
  global $pdo;
  $params = func_get_args();
  $sql = array_shift($params);
  $stmt = $pdo->prepare($sql);
  $stmt->execute($params);
  return $stmt; // so we can fetch(), etc.
}

这篇关于real_escape_string和prepare()之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！