mysql statement how to insert a value with a single quotation mark or backslash

Preface

This article mainly introduces the related contents about mysql statement inserting single quotation marks or backslash values, and the following words are not much to say, let's take a look at the detailed introduction from 1

For example, there is a table, and its 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, insert a record into it. The example 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);

//  Detect a 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 1 piece of code executes OK, and there is no problem. But if title in the code becomes happy valentine 's day! The following error will be reported, prompting you to have a grammatical 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 INSERT INTO activity (title) VALUES ( 'happy valentine's day!'); The single quotation marks in this sql statement are not paired.

Sometimes you will insert some data given by users into the database, which is likely to happen above, so how to avoid it?

To escape the special characters in sql. You can change the 1 line of code for $sql to look like this:

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

The whole 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 in it is converted into unicode code, which is inserted into mysql and found to be eaten.

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

Look at the following sample code:

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

The whole sql string actually looks like this:

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

Insert it into the database, and the value of the field title becomes {"balbalbla":"u4e2du6587"} .

That's because the\ here is used as an escape character. In fact, we have to escape the\ of unicode code again, so that the one inserted into the database is correct

$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