Shulou(Shulou.com)05/31 Report--

This article is about how mysql statements insert values with single quotes or backslashes. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

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

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

For example, insert a record into it. The sample code is as follows:

$servername = "xxxxservername"; $port = 3306 leading username = "xxxusername"; $password = "xxxpwd"; $dbname = "xxxxxxdb"; / / create 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 yearbook yearbook leading role '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 piece of code executes OK, and there is 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 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 daylight')' At line

Because INSERT INTO activity (title) VALUES ('happy valentine's daylighting'); single quotation marks are not paired in this sql statement.

Sometimes some user-given data will be inserted into the database, which is likely to happen, so how to avoid it?

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

$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 daylight'); "

Sometimes there is a problem: after json_encode, the Chinese in it is converted into Unicode code, which is inserted into mysql and found that\ has been eaten.

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

Take a look at 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 treated as an escape character, and it is actually necessary to escape the\ 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\"}'); thank you for reading! This is the end of the article on "how to insert values with single quotes or backslashes in mysql statements". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.