(17) SQL injection and SQL mode, and 17sql injection mode

(17) SQL injection and SQL mode, and 17sql injection mode
Overview

  SQL injection is used to insert user data into the actual database operating language by using some external interfaces of the database, so as to intrude into the database and even the operating system. In the security field,We should never trust users' input.We must make sure that all user input data is insecure, and we all need to filter user input data.No (runtime) compilation, no injection.Therefore, to fundamentally prevent the above-mentioned attacks, we should avoid data being executed and keep the boundaries between code and data at all times. Specifically, for SQL injection, the executed malicious code is compiled through the SQL interpretation engine of the database. Therefore, you only need to avoid the compilation of user input data by the database system.
Unlike other databases, MySQL runs in different SQL Mode and can apply different modes to different clients. In this way, each application can customize the server operation mode as needed. The mode defines which SQL syntaxes should be supported by MySQL and which data verification checks should be performed. This is a bit similar to how apache configures different levels of error logs and reports errors without reporting them.

SQL Injection1. Inject instances

// Php code $ unsafe_variable =$ _ POST ['user _ input']; mysql_query ("insert into 'table' ('column') VALUES ('{$ unsafe_variable }') ");

When the post code is as follows:

value'); DROP TABLE table;--

Query code

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

This will directly Delete the table, and your data will be damaged.

2. Prevent SQL Injection

Method 1
PrepareStatement + Bind-Variable: SQL statements and query parameters are sent to the database server for resolution respectively.
Php has two implementation methods.

// Use PDO (PHP data object) $ stmt = $ pdo-> prepare ('select * FROM employees WHERE name =: name '); $ stmt-> execute (array ('name' => $ name); foreach ($ stmt as $ row) {// do something with $ row} // use mysql extension-mysqli $ stmt = $ dbConnection-> prepare ('select * FROM employees WHERE name =? '); $ Stmt-> bind_param ('s', $ name); $ stmt-> execute (); $ result = $ stmt-> get_result (); while ($ row = $ result-> fetch_assoc () {// do something with $ row}

Method 2
Escape the query statement (the most common method): Use the conversion function provided by the application.

Application	Function
MySQL C API	Mysql_real_escape_string ()
MySQL ++	Escape and quote Modifiers
PHP	Use mysql_real_escape_string () (applicable before PHP4.3.0), and then use mysqli or pdo
Perl DBI	Placeholder or quote ()
Ruby DBI	Placeholder or quote ()

Method 3
Use a self-defined function for verification: in essence, it still performs conversion and filtering of illegal input data.
Input verification can be divided into: 1. Sort data to make it valid; 2. Reject known illegal input; 3. Only receive known valid input.

Method 4
Use stored procedures.
For more information about stored procedures, see (9) mysql stored procedures and user-defined functions.

SQL Server Mode1. SQL mode syntax

# View the current SQL mode select @ SQL _mode; # view the current SQL mode SELECT @ session. SQL _mode; # modify the current SQL mode SET [SESSION] [GLOBAL] SQL _mode = 'modes ';

• The session option indicates that the connection only takes effect. global indicates that the connection does not take effect and the next connection takes effect.
• You can also use "-SQL-mode = 'modes '" To set SQL _mode at MySQL startup.
• You can set it in the configuration file.

2. Common SQL _mode values

ONLY_FULL_GROUP_BY:
For group by aggregation, if the column in SELECT does not appear in group by, this SQL statement is invalid because the column is not in the GROUP BY clause.

NO_AUTO_VALUE_ON_ZERO:
This value affects the insertion of auto-increment columns. By default, insert 0 or NULL to generate the next auto-growth value. If you want to insert a value of 0, and the column is auto-incrementing, this option is useful.

STRICT_TRANS_TABLES:
In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted without any restrictions on non-transaction tables.

NO_ZERO_IN_DATE:
In strict mode, zero date and month are not allowed.

NO_ZERO_DATE:
Set this value. mysql databases do not allow zero-date insertion. If zero-date insertion is performed, an error is thrown instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO:
In the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If this mode is not provided, MySQL returns NULL when data is divided by zero.

NO_AUTO_CREATE_USER:
GRANT is prohibited from creating users with blank passwords.

NO_ENGINE_SUBSTITUTION:
If the required storage engine is disabled or not compiled, an error is thrown. If this value is not set, it is replaced by the default storage engine and an exception is thrown.

PIPES_AS_CONCAT:
Treat "|" as the concatenation operator rather than the or operator of the string, which is the same as that of the Oracle database and similar to the Concat function of the String concatenation function.

ANSI_QUOTES:
When ANSI_QUOTES is enabled, strings cannot be referenced using double quotation marks because it is interpreted as an identifier.

Description

The SQL _mode settings in ORACLE are equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER.

Reference1. SQL Injection

Http://www.zhihu.com/question/22953267
Http://blog.csdn.net/agoago_2009/article/details/37884797

2. SQL Mode

Http://tech.it168.com/a2012/0822/1388/000001388401.shtml
Http://c.biancheng.net/cpp/html/1471.html