mysql 基本操作 触发器、锁、事物、分布式事物、sql安全问题、sql mode详解 （第五章）

触发器

是什么：触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

创建触发器的语法：

CREATE TRIGGER trigger_name trigger_time trigger_event     ON tbl_name FOR EACH ROW trigger_stmt 

注意：触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器。

其中 trigger_time是触发器的触发时间，可以是BEFORE 或者 AFTER，BEFORE 的含义指在检查约束前触发，而 AFTER 是在检查约束后触发。

而 trigger_event 就是触发器的触发事件，可以是 INSERT、UPDATE 或者 DELETE

对同一个表相同触发时间的相同触发事件，只能定义一个触发器。例如，对某个表的不同字段的 AFTER 更新触发器，只能定义成一个触发器，在触发器中通过判断更新的字段进行对应的处理。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

现在触发器还只支持行级触发的，不支持语句级触发。

在样例数据库中，为 film表创建了 AFTERINSERT 的触发器，具体如下：

CREATE TRIGGER ins_film  AFTER INSERT ON film FOR EACH ROW BEGIN     INSERT INTO film_text (film_id, title, description)         VALUES (new.film_id, new.title, new.description); END; $$ delimiter ;

对于有重复记录，需要进行 UPDATE 操作的 INSERT，触发器触发的顺序是 BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE；对于没有重复记录的 INSERT，就是简单的执行 INSERT 操作，触发器触发的顺序是 BEFORE INSERT、AFTER INSERT。对于那些实际执行 UPDATE 操作的记录，仍然会执行 BEFORE INSERT 触发器的内容，在设计触发器的时候一定要考虑这种情况，避免错误地触发了触发器。

 

删除触发器：

drop trigger trigger_name

触发器的使用

触发器执行的语句有以下两个限制。

¡ 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用 CALL 语句的动态 SQL 语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT 或者 INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。

¡ 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、COMMIT 或 ROLLBACK。

¡ MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。

 

事物控制和锁定语句

MySQL 支持对MyISAM 和MEMORY 存储引擎的表进行表级锁定，对BDB 存储引擎的表进行页级锁定，对InnoDB 存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

接下来说下 mysql 的锁的使用

LOCK TABLE 和 UNLOCK TABLE

LOCKTABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。

UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁，具体语法如下：

LOCK TABLES     tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}     [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES 

如表 14-1 所示是一个获得表锁和释放表锁的简单例子，演示的是film_text 表获得 read 锁的情况，其他 session 更新该表记录会等待锁，film_text 表释放锁以后，其他 session 可以进行更新操作。其中 session1 和 session2 表示两个同时打开的 session，表格中的每一行表示同一时刻两个session 的运行状况

表14-1                 一个获得表锁和释放表锁的简单例子 

session_1

session_2

获得表film_text的READ锁定

mysql> lock table film_text read;

Query OK, 0 rows affected (0.00 sec)

 

当前session可以查询该表记录

mysql> select film_id,title from film_text where film_id = 1001;

+---------+------------------+

| film_id | title            |

+---------+------------------+

| 1001    | ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)

其他session也可以查询该表的记录

mysql> select film_id,title from film_text where film_id = 1001;

+---------+------------------+

| film_id | title            |

+---------+------------------+

| 1001    | ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)

 

其他 session 更新锁定表会等待获得锁：

mysql> update film_text set title = 'Test' where film_id =

1001;

等待

释放锁

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

 

Session 获得锁，更新操作完成：

mysql> update film_text set title = 'Test' where film_id =

1001;

Query OK, 1 row affected (1 min 0.71 sec)

Rows matched: 1  Changed: 1  Warnings: 0

事务控制

MySQL 通过SET AUTOCOMMIT、START TRANSACTION、COMMIT 和ROLLBACK 等语句支持本地事务，具体语法如下：

START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}

¡START TRANSACTION 或 BEGIN 语句可以开始一项新的事务。

¡COMMIT 和 ROLLBACK 用来提交或者回滚事务。

¡CHAIN 和 RELEASE 子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。

¡SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

如果只是对某些语句需要进行事务控制，则使用 START TRANSACTION 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改AUTOCOMMIT 来控制事务比较方便，这样不用在每个事务开始的时候再执行 STARTTRANSACTION 语句。

如表 14-2 所示的例子演示了使用 START TRANSACTION 开始的事务在提交后自动回到自动提交的方式；如果在提交的时候使用 COMMIT AND CHAIN，那么会在提交后立即开始一个新的事务。 

session_1

session_2

从表actor中查询actor_id=201的记录，结果为空：

mysql> select * from actor where actor_id =

201;

Empty set (0.00 sec)

从表actor中查询actor_id=201的记录，结果为空：

mysql> select * from actor where actor_id =

201;

Empty set (0.00 sec)

用 start transaction 命令启动一个事务，往表 actor中插入一条记录，没有commit： mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

 

mysql>          insert            into            actor

(actor_id,first_name,last_name) values(201,'Lisa','Tom');

Query OK, 1 row affected (0.00 sec)

 

 

查询表actor，结果仍然为空：

mysql> select * from actor where actor_id =

201;

Empty set (0.00 sec)

执行提交：

mysql> commit;

Query OK, 0 rows affected (0.04 sec)

 

如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock

tables 被执行，如表 14-3所示

session_1

session_2

从表actor中查询actor_id=201的记录，结果为空：

mysql> select * from actor where actor_id =

201;

Empty set (0.00 sec)

从表actor中查询actor_id=201的记录，结果为空：

mysql> select * from actor where actor_id =

201;

Empty set (0.00 sec)

对表actor加写锁：

mysql> lock table actor write;

Query OK, 0 rows affected (0.00 sec)

 

 

对表actor的读操作被阻塞：

mysql> select actor_id,last_name from actor where actor_id = 201;

等待

插入一条记录

mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');

Query OK, 1 row affected (0.04 sec)

等待

回滚刚才的记录： mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

等待

用start transaction命令重新开始一个事务：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

等待

 

session1开始一个事务时，表锁被释放，可以查询： mysql> select actor_id,last_name from actor where actor_id = 201;

+----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 201      | Tom       |

+----------+-----------+

1 row in set (17.78 sec)

对lock方式加的表锁，不能通过rollback进行回滚。

通常情况下，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型表的更新可以被复制到从（Slave）数据库中，所有的 DDL 语句是不能回滚的

在事务中可以通过定义 SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚不同的 SAVEPOINT。需要注意的是，如果定义了相同名字的SAVEPOINT，则后面定义的 SAVEPOINT 会覆盖之前的定义。对于不再需要使用的SAVEPOINT，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT，删除后的 SAVEPOINT，不能再执行 ROLLBACK TO SAVEPOINT 命令。

如表 14-4所示的例子就是模拟回滚事务的一个部分，通过定义 SAVEPOINT来指定需要回滚的事务的位置。

表14-4                             模拟回滚事务 

session_1

session_2

从表actor中查询first_name=’Simon’的记录，结果为空：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

从表actor中查询first_name=’Simon’的记录，结果为空：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

启动一个事务，往表actor中插入一条记录： mysql> start transaction;

Query OK, 0 rows affected (0.02 sec)

 mysql> insert into actor (actor_id,first_name,last_name) values(301,'Simon','Tom');

Query OK, 1 row affected (0.00 sec)

 

可以查询到刚插入的记录：

mysql> select actor_id,last_name from actor where first_name = 'Simon'; +----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 301      | Tom       |

+----------+-----------+

1 row in set (0.00 sec)

无法从actor表中查到session1刚插入的记录：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

定义savepoint，名称为test：

mysql> savepoint test;

Query OK, 0 rows affected (0.00 sec)

 

继续插入一条记录：

mysql> insert into actor (actor_id,first_name,last_name) values(302,'Simon','Cof');

Query OK, 1 row affected (0.00 sec)

 

可以查询到两条记录：

mysql> select actor_id,last_name from actor where first_name = 'Simon'; +----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 301      | Tom       |

| 302      | Cof       |

+----------+-----------+

2 rows in set (0.00 sec)

仍然无法查询到结果：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

回滚到刚才定义的savepoint：

mysql> rollback to savepoint test;

Query OK, 0 rows affected (0.00 sec)

 

只能从表actor中查询到第一条记录，因为第二条已经被回滚：

mysql> select actor_id,last_name from actor where first_name = 'Simon'; +----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 301      | Tom       |

+----------+-----------+

1 row in set (0.00 sec)

仍然无法查询到结果：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

用commit命令提交： mysql> commit;

Query OK, 0 rows affected (0.05 sec)

 

只能从actor表中查询到第一条记录：

mysql> select actor_id,last_name from actor where first_name = 'Simon'; +----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 301      | Tom       |

+----------+-----------+

1 row in set (0.00 sec)

只能从actor表中查询到session1插入的第一条记录：

mysql> select actor_id,last_name from actor where first_name = 'Simon'; +----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 301      | Tom       |

+----------+-----------+

1 row in set (0.00 sec)

分布式事物的使用

1， 原理

应用程序涉及一个或多个资源管理器和一个事务管理器。

资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM 管理的事务。例如，多台 MySQL 数据库作为多台资源管理器或者几台Mysql 服务器和几台 Oracle 服务器作为资源管理器。

·事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识。

MySQL 执行 XA MySQL 时，MySQL 服务器相当于一个用于管理分布式事务中的XA 事务的资源管理器。与 MySQL 服务器连接的客户端相当于事务管理器。

 

要执行一个分布式事务，必须知道这个分布式事务涉及到了哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚时。根据每个资源管理器报告的有关执行情况的内容，这些分支事务必须作为一个原子性操作全部提交或回滚。要管理一个分布式事务，必须要考虑任何组件或连接网络可能会故障。

 

用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。

¡  在第一阶段，所有的分支被预备好。即它们被 TM 告知要准备提交。通常，这意味着用于管理分支的每个RM 会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。

¡  在第二阶段，TM 告知 RMs 是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

在有些情况下，一个分布式事务可能会使用一阶段提交。例如，当一个事务管理器发现，一个分布式事务只由一个事务资源组成（即单一分支），则该资源可以被告知同时进行预备和提交。

分布式事物的语法

分布式事务（XA 事务）的 SQL 语法主要包括：

XA {START|BEGIN} xid[JOIN|RESUME]

XASTART xid 用于启动一个带给定 xid 值的 XA 事务。每个 XA 事务必须有一个唯一的 xid 值，因此该值当前不能被其他的 XA 事务使用。

xid 是一个 XA 事务标识符，用来唯一标识一个分布式事务。xid 值由客户端提供，或由

MySQL 服务器生成。xid 值包含 1～3 个部分：

xid: gtrid [, bqual [,formatID ]]

¡  gtrid 是一个分布式事务标识符，相同的分布式事务应该使用相同的gtrid，这样可以明确知道 xa 事务属于哪个分布式事务。

¡  bqual 是一个分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务，bqual 值必须是唯一的。

¡  formatID 是一个数字，用于标识由 gtrid 和 bqual 值使用的格式，默认值是 1。

下面其他 XA 语法中用到的 xid 值，都必须和 START 操作使用的 xid 值相同，也就是表示对这个启动的XA 事务进行操作。

XA END xid [SUSPEND [FOR MIGRATE]]  XA PREPARE xid 

使事务进入 PREPARE状态，也就是两阶段提交的第一个提交阶段。

XA COMMIT xid [ONE PHASE] XA ROLLBACK xid 

这两个命令用来提交或者回滚具体的分支事务。也就是两阶段提交的第二个提交阶段，分支事务被实际的提交或者回滚

XA RECOVER XA RECOVER返回当前数据库中处于PREPARE 状态的分支事务的详细信息。 

分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时的故障解决。XA 的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等，如表14-5 所示的例子演示了一个简单的分布式事务的执行，事务的内容是在 DB1 中插入一条记录，同时在 DB2 中更新一条记录，两个操作作为同一事务提交或者回滚。

session_1 in DB1

session_2 in DB2

在数据库 DB1 中启动一个分布式事务的一个分支事

务，xid的gtrid为“test”，bqual为“db1”： mysql> xa start 'test','db1';

Query OK, 0 rows affected (0.00 sec)

 

分支事务1在表actor中插入一条记录： mysql> insert into actor (actor_id,first_name,last_name) values(301,'Simon','Tom');

Query OK, 1 row affected (0.00 sec)

 

对分支事务1进行第一阶段提交，进入prepare状态：

mysql> xa end 'test','db1';

Query OK, 0 rows affected (0.00 sec)

 

mysql> xa prepare 'test','db1';

Query OK, 0 rows affected (0.02 sec)

在数据库DB2中启动分布式事务“test”的另外一个分支事务，xid 的 gtrid 为“test”，bqual 为

“db2”：

mysql> xa start 'test','db2';

Query OK, 0 rows affected (0.00 sec)

 

分支事务2在表film_actor中更新了23条记录：

mysql> update film_actor set

last_update=now() where actor_id = 178; Query OK, 23 rows affected (0.04 sec)

Rows matched: 23  Changed: 23  Warnings: 0

 

对分支事务2进行第一阶段提交，进入prepare状态：

mysql> xa end 'test','db2';

Query OK, 0 rows affected (0.00 sec)

 

mysql> xa prepare 'test','db2';

Query OK, 0 rows affected (0.02 sec)

用xa recover命令查看当前分支事务状态：

mysql> xa recover \G

***************************               1.

***************************     formatID: 1 gtrid_length: 4 bqual_length: 3         data: testdb1

1 row in set (0.00 sec)

 

row

用xa recover命令查看当前分支事务状态：

mysql> xa recover \G

***************************              1.

***************************     formatID: 1 gtrid_length: 4 bqual_length: 3         data: testdb2

1 row in set (0.00 sec)

row

两个事务都进入准备提交阶段，如果之前遇到任何错误，都应该回滚所有的分支，以确保分布式事务确。

的正

提交分支事务1：

mysql> xa commit 'test','db1';

Query OK, 0 rows affected (0.03 sec)

提交分支事务2：

mysql> xa commit 'test','db2';

Query OK, 0 rows affected (0.03 sec)

 

两个事务都到达准备提交阶段后，一旦开始进行提交操作，就需要确保全部的分支都提交成功。

 

这分布式事物存在的问题：

如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写binlog，

存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。

使用 mysqlbinlog查看binlog，可以确认最后提交的这个分支事务并没有记录到 binlog中，因为复制和灾难恢复都是依赖于binlog的，所以binlog的缺失会导致复制环境的不同步

，以及使用 binlog 恢复丢失部分数据。 如果分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，如果此时分支事务已经执行到 prepare 状态，

那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。

session_1

session_2

从表actor中查询first_name=’Simon’的记录，结果为空：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

从表actor中查询first_name=’Simon’的记录，结果为空：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

启动分布式事务test：

mysql> xa start 'test';

Query OK, 0 rows affected (0.00 sec)

 

往actor表中插入一条记录：

mysql> insert into actor (actor_id,first_name,last_name) values(301,'Simon','Tom');

Query OK, 1 row affected (0.00 sec)

 

事务结束：

mysql> xa end 'test';

Query OK, 0 rows affected (0.00 sec)

 

查询刚插入的记录，可以显示结果：

mysql> select actor_id,last_name from actor where first_name = 'Simon'; +----------+-----------+

| actor_id | last_name |

+----------+-----------+

| 301      | Tom       |

+----------+-----------+

1 row in set (0.00 sec)

查询刚插入的记录，显示结果为空：

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

 

完成第一阶段提交，进入prepare状态。 mysql> xa prepare 'test';

Query OK, 0 rows affected (0.02 sec)

 

 

 

查询分布式事务“test”的状态：

mysql> xa recover \G

***************************              1.

***************************     formatID: 1 gtrid_length: 4 bqual_length: 3

row

 

        data: test

1 row in set (0.00 sec)

session_1异常中止

 

session_1被回滚。

session1 异常中止后，分布式事务被回滚， session2中无法查询到session1插入的记录，如果此时 session2 存在分支事务并且被成功提交，则会导致分布式事务的不完整。

mysql> select * from actor where first_name =

'Simon';

Empty set (0.00 sec)

如果分支事务在执行到 prepare 状态时，数据库异常，且不能再正常启动，需要使用备份和 binlog来恢复数据，那么那些在prepare 状态的分支事务因为并没有记录到 binlog，所以不能通过 binlog 进行恢复，在数据库恢复后，将丢失这部分的数据。

总之，MySQL 的分布式事务还存在比较严重的缺陷，在数据库或者应用异常的情况下，可能会导致分布式事务的不完整。如果应用对于数据的完整性要求不是很高，则可以考虑使用。如果应用对事务的完整性有比较高的要求，那么对于当前的版本，则不推荐使用分布式事物。

sql中的安全问题

一、sql注入

危害：攻击者可以利用它读取、修改或者删除数据库内的数据，获取数据库中的用户名和密码等敏感信息，甚至可以获得数据库管理员的权限，而且，SQL Injection 也很难防范。网站管理员无法通过安装系统补丁或者进行简单的安全配置进行自我保护，一般的防火墙也无法拦截 SQL Injection 攻击。

http://127.0.0.1/injection/user.php?username=angel'/*

http://127.0.0.1/injection/user.php?username=angel'#

sql 语句，“/*”或者“#”都可以将后面的语句注释掉。结果导致只用用户名和密码的url都能成功登陆。和 or 的不同在于  or 是利用逻辑运算，后者是利用mysql特向。都实现了sql注入的效果。

防御措施：

1.    PHP 5 开始，也在扩展的 MySQLI 中支持 PrepareStatement，所以在使用这类语言作数据库开发时，强烈建议使用PrepareStatement+Bind-variable 来实现，而尽量不要使用拼接的 SQL。

2.   自己定义函数进行校验

想要获得最好的安全状态，就是对用户提交或者可能改变的数据进行简单的分类，用正则表达式对用户输入进行检测和验证

已知非法符号有：“’”、“;”、“=”、“(”、“)”、“/*”、“*/”、“%”、“+”、“”、“>”、“<”、“--”、“[”、“]”

其实只需要过滤非法的符号组合就可以阻止已知形式的攻击，并且如果发现更新的攻击符号组合，也可以将这些符号组合增添进来，继续防范新的攻击。特别是空格符号和与其产生相同作用的分隔关键字的符号，例如“/**/”，如果能成功过滤这种符号，那么有很多注入攻击将不能发生，并且同时也要过滤它们的十六进制表示“％XX”

由此可以构造如下正则表达式：

(|\'|(\%27)|\;|(\%3b)|\=|(\%3d)|\(|(\%28)|\)|(\%29)|(\/*)|(\%2f%2a)|(\*/)|(\%2a%2f)|\+|(\%2b)|\<|(\%3c)|\>|(\%3e)|\(--))|\[|\%5b|\]|\%5d)

根据上述的正则表达式，可以提供一个函数（以PHP举例），可以防范大多数的SQL注入，具体函数如下： 

function SafeRequest ($ParaName, $ParaType) {  /* ---传入参数--- */  /* ParaName:参数名称-字符型 */  /* ParaType: 参数类型-数字型（1 表示参数是数字或字符，0 表示参数为其他）*/   if ($ParaType == 1)  {   $re = "/[^\w+$]/";  }  else  {   $re = "/(|\＇|(\%27)|\;|(\%3b)|\=|(\%3d)|\(|(\%28)|\)|(\%29)|(\/*)|(\%2f%2a)|(\ */)|(\%2a%2f)|\+|(\%2b)|\<|(\%3c)|\>|(\%3e)|\(--))|\[|\%5b|\]|\%5d)/";  }   if (preg_match($re, $ParaName) > 0)  {   echo("参数不符合要求，请重新输入!");   return 0;  }  else  {   return 1;  } } 

Mysql sql mode

一、解决那些问题：

1. 通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效地保障数据准确性。

2.通过设置 SQL Mode 为 ANSI 模式，来保证大多数 SQL 符合标准的 SQL 语法,这样应用在不同数据库之间进行迁移时，则不需要对业务SQL 进行较大的修改。

3.在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使 MySQL 上的数据更方便地迁移到目标数据库中。

比如：插入一个限制长度的字符，会返回warning，自动截断。修改sql_mode 为 STRICT_TRANS_TABLES（严格模式）实现了数据的严格校验，使错误数据不能插入表中，从而保证了数据的准确性

修改表的sql model语句为：

SET [SESSION|GLOBAL] sql_mode='modes'

其中 SESSION 选项表示只在本次连接中生效；而 GLOBAL 选项表示在本次连接中并不生效，而对于新的连接则生效

1.    检验日期数据的合法性：这是sql mode常见的功能，往数据库插入4月31日，（4月没有31日），在不同的sql_mode 的插入结果不同，在ANSI模式下，非法日志可以插入，但是值是0000-00-00 00:00:00，并且系统给出了waring，在TRADITIONAL模式下，提示非法日期，拒绝插入

2.    在 INSERT或 UPDATE 过程中，如果 SQL MODE处于 TRADITIONAL模式，运行MOD(X， 0)会产生错误，因为 TRADITIONAL 也属于严格模式，在非严格模式下 MOD(X，0)返回的结果是 NULL，所以在含有 MOD 的运算中要根据实际情况设定好 sql_mode。

3.    启用 NO_BACKSLASH_ESCAPES 模式，使反斜线成为普通字符。在导入数据时，如果数据中含有反斜线字符，启用NO_BACKSLASH_ESCAPES 模式保证数据的正确性，是个不错的选择。

4.    启用 PIPES_AS_CONCAT 模式。将“||”视为字符串连接操作符，在 Oracle 等数据库中，“||”被视为字符串的连接操作符，所以，在其他数据库中含有“||”操作符的 SQL 在 MySQL 中将无法执行，为了解决这个问题，MySQL 提供了 PIPES_AS_CONCAT 模式。

5.    总结sql mode 值及说明

sql_mode值

描述

ANSI

等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和 ANSI 组合模式，这种模式使语法和行为更符合标准的 SQL

STRICT_TRANS_TA

BLES

STRICT_TRANS_TABLES 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告

TRADITIONAL

TRADITIONAL 模式等同于 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、

NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、TRADITIONAL 和 NO_AUTO_CREATE_USER 组合模式，所以它也是严格模式，对于插入不正确的值是给出错误而不是警告。可以应用在事务表和非事务表，用在事务表时，只要出现错误就会立即回滚

可以发现，表格中第一列 SQL Mode 的值其实都是一些原子模式的组合，类似于角色和权限的关系。这样当实际应用时，只需要设置一个模式组合，就可以设置很多的原子模式，大大方便了用户的工作。 

这次分享的有点多，大家有什么建议可以多多留言，我会一一回复的，谢谢大家。