Escape characters: HTML, MySQL, PostgreSQL, JSON, and PHP

Conclusion:

1. using PHP magic_quotes_gpc is incorrect. This function has been deleted since PHP 5.4.0. Http://www.php.net/manual/zh/function.get-magic-quotes-gpc.php

2. php addslashes is incorrect. Because it does not correspond to MySQL or pgsql. PHP officially strongly recommends using mysqli_real_escape_string () For MySQL and pg_escape_string () for PostgreSQL (). Http://php.net/manual/en/function.addslashes.php

3. Use it before saving it to the databaseHtmlspecialchars is incorrect. It should be saved directly to the database. C ++ does not need to escape HTML. Escape is required only when the web is used. See.

4. UsePHPMysql_escape_string is incorrect and has been deprecated. UseMysqli: real_escape_string. Http://php.net/manual/en/function.mysql-escape-string.php

5. Escape data based on different databases before data is stored in the DaO layer. Controller and model should not be known.

Incorrect escape method:

Correct escape method:

 

The detailed analysis is as follows.

Why escape:

1. Conflict between characters and reserved words, such as small characters and numbers in HTML <used for tag

2. the ASCII character set does not contain this character, for example, in HTMLI used to write & copy;

Currently, the Unicode Character Set is supported for Windows, Linux, and GNU glibc 2nd.ProgramUnicode UTF-8 encoding is used in most cases.

This article only discusses the 1st cases.

Content to be escaped:

	Escape	Escape characters (Common Errors)
Html	<,&	>
MySQL	Line breaks, carriage returns, etc.	Page feed, space, <,> ,&
PostgreSQL	Page feed, line feed, and carriage return
JSON	", \ And control characters

Common PHP escape methods:

	Escape range
Addslashes should not be used	Single quotation marks, double quotation marks, backslash, and NUL
Htmlspecialchars	&, ', ", <,>
Mysql_escape_string should not be used	Corresponding to MySQL 4
Mysqli: real_escape_string	Corresponding to MySQL 5 +
Pg_escape_string	Corresponding to PostgreSQL
Php pdo: quote	Automatically determines whether the database is MySQL or PostgreSQL.
PDO: Prepare ()	Automatically determines whether the database is MySQL or PostgreSQL.

Escape the string "Hello world \ fjim \ n \ r" and the result is as follows:

Mysqli: real_escape_string

View code

<? PHP $ Mysqli = New Mysqli ('localhost', 'root', '1', 'test' );  $ = "Hello world \ fjim \ n \ r" ;  Var_dump ( $ Mysqli -> Real_escape_string ( $  ));  $ SQL = "Insert into user values ('2', 'Lucy ','". $ Mysqli -> Real_escape_string ( $ ).'\');';  Var_dump ( $ SQL  );  $ Mysqli -> Query ( $ SQL  ); ?>

PDO: quote MySQL and mysqli: real_escape_string have the same results.

View code

<? PHP  $ DSN = 'Mysql: dbname = test; host = localhost' ;  $ User = 'Root';  $ Password = '1' ;  $ DBH = New PDO ( $ DSN , $ User , $ Password  );  $ DBH -> Setattribute (PDO: attr_errmode, PDO :: Errmode_exception );  $ = "Hello world \ fjim \ n \ r" ;  Var_dump ($ DBH -> Quote ( $  ));  $ SQL = "Insert into user values ('2', 'Lucy ',". $ DBH -> Quote ( $ ).');' ;  Var_dump ( $ SQL  );  $ Stmt = $ DBH -> Query ( $ SQL  ); Exit  ; ?>

 

 

Pg_escape_string

View code

<?PHP$ DB= Pg_connect ("host = localhost Port = 5432 dbname = test user = Root Password = 1");$= "Hello world \ fjim \ n \ r";Var_dump(Pg_escape_string ($));$ SQL= "Insert into schema1.user values ('2', 'Lucy ','". pg_escape_string ($).'\');';Var_dump($ SQL); Pg_query ($ DB,$ SQL);Exit;?>
 

PDO: quote pgsql and pg_escape_string have the same results.

View code

<? PHP  $ DSN = 'Pgsql: host = localhost; Port = 5432; dbname = test; user = root; Password = 1' ;  $ DBH = New PDO ( $ DSN  );  $ DBH -> Setattribute (PDO: attr_errmode, PDO :: Errmode_exception );  $ = "Hello world \ fjim \ n \ r" ; Var_dump ( $ DBH -> Quote ( $  ));  //  $ Stmt = $ DBH-> query ('select "ID", "name", "DESC" from schema1. "user "'); // $ SQL = "insert into schema1.user values ('1', 'Jim ', 'Hello world \ fjim \ n \ R ');";  $ SQL = "Insert into schema1.user values ('2', 'Lucy ',". $ DBH -> Quote ( $ ).');' ;  Var_dump ( $ SQL );  $ Stmt = $ DBH -> Query ( $ SQL  );  Exit  ; ?>

 

 

 

HTML:

HTML tags start with "small" and "Number", so they must be escaped. The "big" and "Number"> do not need to be escaped. Official documentation:Http://www.w3.org/TR/xhtml1/#h-4.8

 

MySQL:

Official documents: http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html#string-syntax

MySQL in the default empty SQL-mode, the value of the string is caused by single quotation marks or double quotation marks, official documentation: http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html#string-syntax

MySQL in the default empty SQL-mode, the identifier (library, table, index, column, and alias) is caused by a backslash, official documentation: http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html#legal-names

MySQL in ansi_quotes SQL-mode, the value of the string is caused by single quotes, official documentation: http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html#string-syntax

MySQL in ansi_quotes SQL-mode, the identifier (library, table, index, column, and alias) is caused by double quotation marks "or backslash", http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#server-sql-mode

The default empty SQL-mode is used as an example.

If the string is enclosed in double quotation marks, the single quotation marks in the string do not need to be escaped. If the string is enclosed by single quotes, the double quotation marks in the string do not need to be escaped.

When using musqldump to export SQL statements, you can see that MySQL strings are enclosed by single quotation marks, and double quotation marks must be escaped.

For example, insert into 'asdf 'values ('a \ "B '); and insert into 'asdf 'values ('A" B'); the result is the same.

 

PostgreSQL:

Official documents: http://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E5%9B%9B%E7% AB %A0#C.E8.AF.AD.E8.A8.80.E4.B8.AD.E7.9A.84.E8.BD.AC.E6.84.8F.E5.AD.97.E7.AC.A6.E4.B8.B2.E5.B8.B8.E9.87.8F.28String_Constants_with_C-style_Escapes.29

The string of the value is enclosed in single quotes.

The identifier (column name, etc.) is enclosed in double quotation marks.

 

JSON:

Official documents: http://www.json.org/json-zh.html

JSON string, which must be enclosed by double quotation marks. If double quotation marks appear in the string, escape them, for example, {"name": "John \" Cliff \ "barxter "}.

 

References:

Http://www.ibm.com/developerworks/cn/linux/i18n/unicode/linuni/

Http://msdn.microsoft.com/zh-cn/magazine/cc163490.aspx

the Unicode-Supported Single Source Code library is used to shorten the development time, unicode brings obvious benefits to Microsoft. For Windows 2000, it may take several months to prepare the localized version of an English product. For Windows XP, this cycle has been shortened to weeks.