在PHP中，如何在插入MySQL表之前转义字符串中的单引号？

I have a lot of text to insert into a MySQL table using PHP. Part of the text looks like this example:

我有很多文本要使用PHP插入MySQL表。部分文本如下所示：

Yes, this is 'great'!

To fill this into an SQL Statement, I need to escape the '.

要将其填充到SQL语句中，我需要转义'。

I'm using an ereg-replace $text=mb_ereg_replace("'","\\'", $text); to make the following work:

我正在使用ereg-replace $ text = mb_ereg_replace（“'”，“\\'”，$ text）;做以下工作：

$sql="insert into mytable (msg) values ('".$text."')";

Now I found out that there is also another text-style, where I have to save to MySQL something like this:

现在我发现还有另一种文本样式，我必须保存到MySQL这样的东西：

As you can see the \' world\' is a "disc"!

So I tried adding more mb_ereg_replace like this:

所以我尝试添加更多mb_ereg_replace，如下所示：

$text=mb_ereg_replace("'","\\'", $text);
$text=mb_ereg_replace("\\","\\\\", $text);

But this does not work, I just get the error message: PHP Warning: mb_ereg_replace(): mbregex compile err: end pattern at escape in [...]

但这不起作用，我只是得到错误消息：PHP警告：mb_ereg_replace（）：mbregex编译错误：在[...]转义时的结束模式

What causes this? I probably made some mistake, but can't find it!

是什么导致这个？我可能犯了一些错误，但找不到它！

Thank you for any kind of help.

谢谢你的帮助。

2 个解决方案

#1

Use mysql_real_escape_string to escape your strings.

使用mysql_real_escape_string来转义字符串。

$text = mysql_real_escape_string($text);

Or better, use PDO and parameterized queries.

或者更好的是，使用PDO和参数化查询。

#2

There's a much better way, and you won't need to worry about escaping your strings ever again. Using prepared statements in mysqli or PDO will both make large queries (ones with many rows) run much faster, they are secure, you don't have to worry about (most types of) SQL injection and they are easy to learn. the strings will just be accepted as is into your database without the risk of breaking your code.

有一个更好的方法，你不必担心再次逃避你的字符串。在mysqli或PDO中使用预处理语句将使大型查询（具有多行的查询）运行得更快，它们是安全的，您不必担心（大多数类型）SQL注入，并且它们易于学习。字符串只会被接受到您的数据库中，而不会有破坏您的代码的风险。

Here's an example with mysqli:

这是mysqli的一个例子：

$conn = new mysqli($servername, $username, $password, $dbname);

$stmt = $conn->prepare("INSERT INTO table (columnname) VALUES (?)");
$stmt->bind_param("s", $text);
$stmt->execute();
$stmt->close();

Basicallly, by binding the parameters before it goes in, it just accepts any string the way you create it, and there's no need to escape anything.'

基本上，通过在参数进入之前绑定参数，它只是按照你创建它的方式接受任何字符串，并且不需要转义任何东西。

Here's the same thing using PDO. This does essentially the same thing but has the advantage of both working with multiple different database types (such as for instance Oracle or PostgreSQL) and also lends itself to some nifty modification due to the classes associated.

这是使用PDO的相同的事情。这基本上是相同的，但具有使用多种不同数据库类型（例如Oracle或PostgreSQL）的优点，并且由于与之关联的类，还可以进行一些漂亮的修改。

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $conn->prepare("INSERT INTO table (columname)
        VALUES (:text)");
        $stmt->bindParam(':text', $text);
        $stmt->execute();
    catch(PDOException $e)
        {
        echo "Oops, didn't work: " . $e->getMessage();
        }
    $conn = null;

#1