How to insert a value containing single quotes or backslashes in MySQL statements

Preface

This article mainly introduces the relevant content about MySQL statements inserting values ​​containing single quotes or backslashes. Let's take a look at the detailed introduction.

For example, there is a table whose structure is like this

CREATE TABLE `activity` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `title` varchar(255) NOT NULL COMMENT 'Activity title',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Activity table';

For example, to insert a record into it, the sample code is as follows:

$servername = "xxxxservername";
$port = 3306;
$username = "xxxusername";
$password = "xxxpwd";
$dbname = "xxxxxxdb";

// Create a connection $conn = new mysqli($servername, $username, $password, $dbname, 8306);

// Check connection if ($conn->connect_error) {
 die("connect failed: " . $conn->connect_error);
}

$item['title'] = 'happy new year!';
$sql = sprintf("INSERT INTO activity (title) VALUES ( '%s');", $item['title']);
var_dump($sql);
if ($conn->query($sql) === TRUE) {
	echo "insert success\n";
} else {
 echo "insert failed:" . $conn->error;
}

$conn->close();

This code executes OK, no problem. But if the title in the code becomes happy valentine's day!, the following error will be reported, indicating that you have a syntax error:

insert failed:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's day!')' at line

Because the single quotes in the SQL statement INSERT INTO activity (title) VALUES ( 'happy valentine's day!'); are not paired.

Sometimes we insert some user-given data into the database, and the above situation is likely to occur. So how can we avoid it?

You need to escape the special characters in sql. You can change the $sql line of code to the following:

$sql = sprintf("INSERT INTO activity (title) VALUES ( '%s');", mysqli_real_escape_string($conn, $item['title']));

The entire sql string actually looks like this:

INSERT INTO activity (title) VALUES ( 'happy valentine\'s day!');"

Sometimes there is a problem: after json_encode, the Chinese characters in it are converted into unicode code, and when inserted into mysql, it is found that \ is eaten.

For example, the unicode code of the two Chinese characters is \u4e2d\u6587, but sometimes when inserted into the database, the backslash is eaten up and becomes u4e2du6587

See the following sample code:

$item['title'] = json_encode([
  'balbalbla' => 'Chinese'
]);
$sql = sprintf("INSERT INTO activity (title) VALUES ( '%s');", $item['title']);

The entire sql string actually looks like this:

INSERT INTO activity (title) VALUES ( '{"balbalbla":"\u4e2d\u6587"}');

When inserted into the database, the value of the title field becomes {"balbalbla":"u4e2du6587"} .

That's because the \ here is used as an escape character. In fact, the unicode code \ must be escaped again so that it can be inserted into the database correctly.

$item['title'] = json_encode([
  'balbalbla' => 'Chinese'
]);
$sql = sprintf("INSERT INTO activity (title) VALUES ( '%s');", mysqli_real_escape_string($conn, $item['title']));

The entire sql string actually looks like this:

INSERT INTO activity (title) VALUES ( '{\"balbalbla\":\"\\u4e2d\\u6587\"}');

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.