Mysql PHP插入引用和双引号。

I am trying to insert data into a table that contains both quotes and double quotes. How can i escape them?

我试图将数据插入包含引号和双引号的表中。我怎样才能逃脱呢?

The value i want to add is:

我要添加的值是:

 default=1,name='Bob',lastName="Jones"


 $ins = "INSERT into table (field1,field2,fiel3,) values ('data1','data2',????)";

If i use single quote, the Bob part gets broken. If I use double quote, the Jones part gets broken.

如果我用单引号，Bob部分就会被破坏。如果我用双引号，琼斯部分就断了。

If i use mysql_escape_string what would this look like?

如果我使用mysql_escape_string它会是什么样子?

$data3 = mysql_escape_string(????);

Again if i use single quote, the Bob part gets broken. If I use double quote, the Jones part gets broken.

如果我用单引号，Bob部分就会被破坏。如果我用双引号，琼斯部分就断了。

I know its a terrible db design but I have no control over the fields i need to add or the syntax of the values. I have to insert the value exactly as seen above.

我知道它是一个糟糕的数据库设计，但是我无法控制我需要添加的字段或值的语法。我必须像上面看到的那样插入值。

3 个解决方案

#1

Use prepared statements

使用预处理语句

Look at this.

看看这个。

First connect with improved mysqli

首先连接改进的mysqli。

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

$stmt = $mysqli->prepare("INSERT into table (field1,field2,fiel3,) values (?,?,?)");
$stmt->bind_param("sss",$data1,$data2,$data3);
$stmt->execute();

Where $mysqli is your connector.

$mysqli是您的连接器。

In this way you have also prevented from SQL Injection.

这样，您还可以避免SQL注入。

#2

From what I have read mysql_* functions are deprecated as of PHP 7, and from what I understand you can't escape quotes. At least not properly. I'd recommend you go to PDO, as it's compatible with different database types, and it's in my opinion easier to use than the mysqli_* functions. Although I'll let you know that mysqli is capable of escaping quotes.

从我所读到的，mysql_*函数被弃用为PHP 7，并且从我所理解的您无法避免引用。至少不正常。我建议您去PDO，因为它与不同的数据库类型兼容，而且在我看来，它比mysqli_*函数更容易使用。尽管我会让你知道mysqli能够逃脱引号。

Since there is already an answer with a mysqli example, here is a short example of using PDO to escape quotes:

因为已经有了一个mysqli示例的答案，下面是一个使用PDO来避免引用的简短示例:

$dns = "mysql:dbname=test;host=localhost";
$user = "root";
$password = "";

$connection = new PDO($dns, $user, $password);

$sql = "INSERT INTO users (username, password) VALUES (:username, :password)";
$statement = $connection->prepare($sql);
$statement->execute([
    ':username' => $username,
    ':password' => $password
]);

Now remember this only escapes quotes, it does not filter your inputs. So before you insert any foreign data(form data), use the filter_var() function based on what you want to filter, e.g: $string = filter_var($string, FILTER_SANITIZE_STRING). Or you could do:

请记住，这只是转义引号，它不会过滤你的输入。因此，在插入任何外部数据(表单数据)之前，请根据您想要筛选的内容使用filter_var()函数。$string = filter_var($string, FILTER_SANITIZE_STRING)。或者你可以做的:

 $statement->bindParam(':username', $username, PDO_PARAM_STRING);

#3

Just a little function which mimics the original mysql_real_escape_string but which doesn't need an active mysql connection. Could be implemented as a static function in a database class. Hope it helps someone.

只是一个小函数，它模仿了原始的mysql_real_escape_string，但它不需要活动的mysql连接。可以在数据库类中作为静态函数实现。希望它能帮助一些人。

<?php 

function mysql_escape_mimic($inp) { 

    if(is_array($inp)) 
        return array_map(__METHOD__, $inp); 

    if(!empty($inp) && is_string($inp)) { 
        return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $inp); 
    } 

    return $inp; 
} 
?>

Source: php manual

来源:php手册

#1