WEB third-party application SQL statement Security Specification

1. Purpose

With the increasing number of RDS users, more and more applications begin to use RDS data for data storage. Many applications are directly or indirectly related to money, therefore, code security for third-party Application WEB Systems and SQL-related coding specifications become more and more important.
This specification is designed to help RDS users deal with SQL injection, database detaching, and data leakage attacks. It helps users fix SQL Injection Vulnerabilities in WEB Systems and jointly protect user data security.

2. Scope of use

All third-party applications connected to RDS

3. encoding principles

SQL injection is caused by the absence of data and structure separation for the SQL statements used in the program. As a result, the SQL statements of the program can be controlled by malicious users, resulting in database information leakage and modification, server intrusion and other serious consequences.

The essence of injection attacks is to execute user input data as code. There are two key conditions,

1) The first is that the user can control the input. 2) The second is the code to be executed by the original program, splicing the data entered by the user.

4. SQL syntax Specification

1. Use less null values as the query condition. There are two reasons: 1) the null value will not be able to use the index, it will affect the database performance, as far as possible in the default value, when inserting data automatically let the database fill in the default value 2) it is easy to generate a null value permanent, thus triggering interception rule example: original statement: select * from employee where flag is null; Improved statement: select * from employee where flag = 0 (enable the database to automatically default to 0)

2. the numeric SQL query adopts a clear numeric forced type conversion for two reasons: 1) the original business logic itself is a digital INT type, and the forced conversion does not affect normal business 2) after mandatory type conversion of numeric parameters, attackers can prevent injection of additional parameters other than numbers. Example: original statement: $ id = $ _ GET ['id']; "select * from employee where id = $ id"; Improved statement: $ id = intval ($ _ GET ['id']); "select * from employee where id = $ id ";

3. the corresponding encoding escape operation is used for SQL queries of the simplified type. The reason is. 1) the original business logic itself is of the STRING type, which does not affect the business operation after escaping. 2) escape Character Parameters to prevent the injection of closed characters such as single quotation marks, double quotation marks, and backslash. This is an effective method for defense against injection: original statement: $ name = $ _ GET ['name']; "select * from employee where name = '$ name'"; Improved statement: $ name = mysql_real_escape_string ($ _ GET ['id']); "select * from employee where name = $ name"; Ps: 1. when using the escape function, be sure to check whether the encoding format of the current script language is consistent with that of the database. For example, when PHP and Mysql are both encoded as GBK or UTF8, escape functions can effectively defend against injection attacks. Otherwise, attackers may bypass injection attacks. when connecting to the database, you need to execute the following statement for unified encoding: // For example, your database uses GBK encoding, your connection code should be written in a format similar to the following $ mysqli = new mysqli ("localhost", "root", "111", "test", 3306 ); $ mysqli-> set_charset ('gbk'); // For example, your database uses UTF-8 encoding, your connection code should be written in a format similar to the following $ mysqli = new mysqli ("localhost", "root", "111", "test", 3306 ); $ mysqli-> set_charset ('utf8 ');

4. Avoid the occurrence of permanent and permanent conditions in SQL statements that are often used by hackers for injection detection. Hackers can use the execution of permanent conditions to determine whether the current WEB application is likely to be injected.

5. in SQL, it is strongly recommended that you use parameterized queries for SQL statements used in the program, using Variable binding to separate data and structures is a good way to defend against SQL injection. Example code: <? Php $ id = $ _ GET ['id']; $ conn = mysql_connect ("localhost", "root", "") or die ("wrong! "); $ Sel = mysql_select_db (" mydb ", $ conn); $ SQL =" select * from user where id = ". id $ que = mysql_query ($ SQL, $ conn);?> Example: <? Php $ id = $ _ GET ['id']; $ conn = mysql_connect ("localhost", "root", "") or die ("wrong! "); $ Sel = mysql_select_db (" mydb ", $ conn); $ SQL =" select * from user where id =: id "$ stmt = $ conn-> prepare ($ SQL); $ stmt-> execute (array (': id' => $ id);?>

5. Cases and scenarios

1. Access to system tables is not allowed

Rational SQL statement 1) select * from information_schema.COLUMNS; this statement druid is considered legal because it does not have injection points, and the only purpose of the SQL statement itself is to query the table, SQL statement 1) SELECT idFROM adminWHERE id = 1 AND 5 = 6 UNIONSELECT concat (0x5E252421, COUNT (8), 0x2A5B7D2F) FROM (SELECT 'column _ name', 'Data _ type', 'character _ set_name 'FROM 'information _ scheme '. 'columns' WHERE TABLE_NAME = between AND TABLE_SCHEMA = 0x796971696C61695F757466) t this statement druid is considered to be an illegal injection attack because the SQL statement uses union concatenation in the clause (WHERE the injection point may be, copy the code and copy the code. do not allow access to SQL statements with reasonable system variables. 1) select @ basedir; druid does not intercept this statement. Because there is no injection point, it is impossible for hackers to inject attacks, should be classified in normal business SQL statements that require unreasonable 2) SELECT * FROM cnp_news where id = '23' and len (@ version)> 0 and '1' = '1' the statement druid will intercept. Attackers can use a logical expression in the clause to conduct illegal detection, inject, copy, and copy code 3. SQL statement 1) select load_file ('\ etc \ passwd'); druid does not intercept this statement, the key to SQL injection is the injection point. This statement does not exist. Therefore, it can only be an SQL statement with unreasonable business requirements. 1) select * from admin where id = (SELECT 1 FROM (select sleep (0) A); druid intelligently detects this sensitive function in "where clause node, the "where clause node" is often used by hackers as an SQL injection point. Therefore, druid intercepts the copy code and copies the Code 4. disable SQL statements with reasonable permanent conditions 1) normal business statements

SELECT F1, F2 from admin where 1 = 1; -- allow SELECT F1, F2 from admin where 0 = 0; -- allow SELECT F1, F2 from admin where 1! = 0; -- allow SELECT F1, F2 from admin where 1! = 2; -- allow

 

Unreasonable SQL statements

1) aggressive SQL statements

Select * from admin where id =-1 OR 17-7 = 10; -- intercept select * from admin where id =-1 and 1 = 2 -- intercept select * from admin where id =-1 and 2> 1 -- intercept select * from admin where id =- 1 and 'A '! = 'B' -- intercept select * from admin where id =-1 and char (32)> char (31) -- intercept select * from admin where id =-1 and '1' like '1' -- intercept select * from admin where id =-1 and 17-1 = 10 -- intercept select * from admin where id =-1 and NOT (1! = 2 AND 2! = 2) -- intercept select * from admin where id =-1 and id like '%' -- intercept select * from admin where id =-1 and length ('abcde')> = 5 -- intercept

 

6. Technical FAQ for any defense system, the demarcation line before effective interception and false interception is always a process of continuous optimization. As a result, some normal business SQL statements may occur, which may be intercepted due to triggering our interception rules. The following lists some SQL statements that may be blocked: 1. 0x1) SQL statement

Select count (*) FROM (SELECT header. id, header. tenant_id, header. create_date header_create_date, header. modify_date header_modify_date, header. supplier_id, header_detail.amount header_amount, header_detail.sku_id header_sku_idFROM purchase_order_header headerINNER JOIN purchase_order_detail header_detail ON (header. id = header_detail.pid) WHERE approve = 1) headerLEFT JOIN (SELECT arrive. id arrive_id, arrive. create_date arrive_create_date, arrive. modify_date arrive_modify_date, arrive_detail.qty arrive_qty, arrive_detail.sku_id arrive_sku_id, arrive. pid arrive_pidFROM purchase_arrive_order_header arriveINNER JOIN purchase_arrive_order_detail arrive_detail ON (arrive. id = arrive_detail.pid) WHERE approve = 1) arrive ON (header. id = Response header_sku_id = Response) left join (SELECT response return_amount, response return_qty, return_header.id return_id, response return_tenant_id, response return_sku_id, response, response return_create_date, response response return_headerINNER JOIN response return_detail ON (response = return_detail.pid) WHERE approve = 1) return_header ON (tenant_id = Response supplier_id = Response return_sku_id = header_sku_id) WHERE 1 = 1 OR header_modify_date> = '2017-03-13 07:53:30 'OR arrive_create_date> = '2017-03-13 07:53:30' OR return_create_date> = '2017-03-13 07:53:30' OR header_modify_date <'2014-03-13 08:04:00 'OR arrive_modify_date <'2014-03-13 08:04:00' OR return_modify_date <'2014-03-13 08:04:00 '2) blocking reason WHERE 1 = 1 permanent violation rule select alway true condition not allow

 

2. 0x2 1) SQL statement SELECT id, name, IFNULL (parentId, 0) parentId, isParentFROM goods_categoryWHERE companyId = 17 UNION ALL SELECT 0, 'all category', NULL, null from dual 2) blocking reason union all select 0, 'all category', NULL, null from dual violates the subquery injection rule union all query not ins 'from clause' 3. 0x3: delete from tds_permission; insert into tds_permission (id, name, memo, permission_key) VALUES (1, 'homepage', NULL, NULL), (2, 'Shop diagnoses ', NULL, NULL), (3, 'shop monitoring', NULL, NULL), (4, 'order Center', NULL, NULL), (5, 'marketing activity', NULL, NULL), (6, 'sales promotion management', NULL, NULL), (7, 'customer management', NULL, NULL), (8, 'System management', NULL, NULL); delete from tb_role; insert into tb_role (id, name, memo) VALUES (100000, 'admin', NULL), (100001, 'normal user', NULL); -- Role _ permission. ordinary users have less "System Management" permission than administrators: delete from tb_role_permission; insert into tb_role_permission (role_id, permission_id) VALUES (100000, 1), (100000, 2), (100000, 3), (100000, 4), (100000, 5), (100000, 6), (100000, 7), (100000, 8), (100001, 1 ), (100001, 2), (100001, 3), (100001, 4), (100001, 5), (100001, 6), (100001, 7); 2) interception reason 2.1) -- Role _ permission. A common user has one "System Management" permission less than the Administrator: note 2.2 appears in the SQL statement.) an SQL request contains multiple SQL statements, that is, stack query, this is a common attack method used by hackers to copy code. As long as there is a dream, the heart will fly.