SQL injection examples and how to prevent SQL injection

Overview

sql injection is to use the external interface of some databases to insert user data into the actual database operation language. In order to achieve the purpose of invading the database and even the operating system. In the security field, we should never trust user input. We must determine that the data entered by the user is unsafe, and we all need to filter the data entered by the user. Without (runtime) compilation, there is no injection. So the way to fundamentally prevent the above types of attacks is to prevent data from being turned into code and be executed, and to always distinguish the boundaries between code and data. As for SQL injection specifically, the executed malicious code is compiled through the SQL interpretation engine of the database, so it only needs to prevent the data entered by the user from being compiled by the database system.   Unlike other databases,
MySQL can run in different SQL Modes (SQL server modes) and can apply different modes to different clients. This way each application can customize the server's operating mode according to its own needs. The schema defines which SQL syntax MySQL should support, and what kind of data validation checks should be performed. This is somewhat similar to apache configuring different levels of error logs, which errors are reported and which errors are not reported.

SQL injection

1. Injection example

//php代码
$unsafe_variable = $_POST['user_input'];   
mysql_query("INSERT INTO `table` (`column`) VALUES ('{$unsafe_variable}')");

When the code in the post is as follows:

value'); DROP TABLE table;--

QueryThe code becomes

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

This will directly

delete the table and your data will be destroyed.

2. Prevent sql injection

Method 1prepareStatement+Bind-Variable: SQL statements and query parameters are sent to the database server for parsing respectively.
There are two implementation methods for php.

//使用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  
}

//使用mysql扩展-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 2Escape the query statement (the most common way): use the conversion
function provided by the application. |Application|Function|

|--------|
|MySQL C 
API
|
mysql_real_escape_string
()|
|MySQL++|escape和quote修饰符|
|PHP|使用mysql_real_escape_string()(适用于PHP4.3.0以前),之后可以使用mysqli或pdo|
|
Perl
 DBI|placeholder或quote()|
|Ruby DBI|placeholder或quote()|

Method 3Use your own
define function for verification: its essence is still illegal for input Data is escaped and filtered. Input validation can be divided into: 1. Organize data to make it valid; 2. Reject known illegal input; 3. Only accept known legal input.

Method 4
Use stored procedures. For stored procedures, see: (9) Stored procedures and
custom functions in mysql

sql server mode

1.sql mode Syntax

#查看当前sql模式
select @@sql_mode;
#查看当前sql模式
SELECT @@session.sql_mode;
#修改当前sql模式
SET [SESSION][GLOBAL] sql_mode='modes';

• The session option means that it will only take effect in this connection; and global means that it will not take effect in this connection and will take effect in the next connection.

• You can also use "--sql-mode='modes'" to set sql_mode when MySQL starts.

• can be set in the

  config file.

  2.sql_mode common values

  ONLY_FULL_GROUP_BY：For GROUP BY
  aggregation operation, if the column in SELECT is not in GROUP appears in BY, then this SQL is illegal because the column is not in the GROUP BY clause.

NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of auto-growing columns. Under the default settings, inserting 0 or NULL represents generating the next auto-increasing value. This option is useful if the user wants to insert a value of 0 and the column is auto-increasing.

STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transaction table, the current operation will be interrupted, and there will be no limit on non-transaction tables.

NO_ZERO_IN_DATE: In strict mode, zero days and months are not allowed.

NO_ZERO_DATE: Set this value. MySQL database does not allow the insertion of zero dates. Inserting zero dates will throw an error instead of a warning.

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

NO_AUTO_CREATE_USER: Prohibits GRANT from creating users with empty passwords.

NO_ENGINE_SUBSTITUTION: Throw an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is used instead and an exception is thrown.

PIPES_AS_CONCAT:
Treat "||" as a concatenation of strings operators instead of or operators , which is the same as the Oracle database, and is similar to the string concatenation function Concat.

ANSI_QUOTES:
With ANSI_QUOTES enabled, you cannot quote a string with double quotes because it is interpreted as an identifier.

Description

ORACLE's sql_mode setting is equivalent to: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Boolean Education Yan Shiba mysql introductory video tutorial

The above is the detailed content of SQL injection examples and how to prevent SQL injection. For more information, please follow other related articles on the PHP Chinese website!