mysql中的锁和mysql的安全问题

6.1 如何加锁
锁定表的语法：
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
解锁语法：
UNLOCK TABLES
innodb的存储引擎提供行级锁，支持共享锁和排他锁两种锁定模式，以及四种不同的
隔离级别。
www.163.com
网易技术部 22
6.2 死锁
InnoDB自动检测事务的死锁，并回滚一个或几个事务来防止死锁。InnoDB不能在MySQL
LOCK TABLES设定表锁定的地方或者涉及InnoDB之外的存储引擎设置锁定的地方检测死锁。
你必须通过设定innodb_lock_wait_timeout系统变量的值来解决这些情况。如果要依靠锁等
待超时来解决死锁问题，对于更新事务密集的应用，将有可能导致大量事务的锁等待，导致
系统异常，所以不推荐在一个事务中混合更新不同存储类型的表，也不推荐相同类型的表采
用不同的锁定方式加锁。
6.3 事务控制
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}
默认情况下，mysql是autocommit的，如果需要通过明确的commit和rollback来提交和
回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和oracle的事务管理明显不
同的地方，如果应用是从oracle数据库迁移到mysql数据库，则需要确保应用中是否对事务
进行了明确的管理。
START TRANSACTION或BEGIN语句可以开始一项新的事务。
COMMIT和ROLLBACK用来提交或者回滚事务。
CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，chain会立即启动
一个新事物，并且和刚才的事务具有相同的隔离级别，release则会断开和客户端的连接。
SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置
之后的所有事务都需要通过明确的命令进行提交或者回滚。
如果我们只是对某些语句需要进行事务控制，则使用START TRANSACTION开始一个事务
www.163.com
网易技术部 23
比较方便，这样事务结束之后可以自动回到自动提交的方式，如果我们希望我们所有的事务
都不是自动提交的，那么通过修改AUTOCOMMIT来控制事务比较方便，这样不用在每个事务开
始的时候再执行START TRANSACTION。
time session_1 session_2
----
----
----
----
----
----
----
----
----
----
----
----
----
----
->
mysql> select * from tt3;
Empty set (0.00 sec)
mysql> select * from tt3;
Empty set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00
sec)
mysql> insert into tt3
values('1',1);
Query OK, 1 row affected (0.03
sec)
mysql> select * from tt3;
Empty set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.05
sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
+------+------+
1 row in set (0.00 sec)
www.163.com
网易技术部 24
mysql> insert into tt3
values('2',2);
Query OK, 1 row affected (0.04
sec)
这个事务是按照自动提交执行的
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
| 2 | 2.00 |
+------+------+
2 rows in set (0.00 sec)
www.163.com
网易技术部 25
mysql> start transaction;
Query OK, 0 rows affected (0.00
sec)
mysql> insert into tt3
values('3',3);
Query OK, 1 row affected (0.00
sec)
mysql> commit and chain;
Query OK, 0 rows affected (0.05
sec)
自动开始一个新的事务
mysql> insert into tt3
values('4',4);
Query OK, 1 row affected (0.00
sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
| 2 | 2.00 |
| 3 | 3.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.06
sec)
www.163.com
网易技术部 26
开始一个事务，会造成一个隐含的unlock tables被执行：
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
4 rows in set (0.00 sec)
time session_1 session_2
----
----
----
----
----
----
----
----
----
----
----
----
----
----
->
mysql> select * from tt3;
Empty set (16.65 sec)
mysql> select * from tt3;
Empty set (16.65 sec)
mysql> lock table tt3 write;
Query OK, 0 rows affected (0.00
sec)
mysql> select * from tt3;
等待
mysql> insert into tt3
values('1',1);
Query OK, 1 row affected (0.07
sec)
等待
mysql> rollback;
Query OK, 0 rows affected (0.00
sec)
等待
www.163.com
网易技术部 27
因此，在同一个事务中，最好不使用不同存储引擎的表，否则rollback时需要对非事务
类型的表进行特别的处理，因为commit、rollback只能对事务类型的表进行提交和回滚。
通常情况下，只对提交的事务纪录到二进制的日志中，但是如果一个事务中包含非事务
类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型表的更新可以被复
制到从的数据库中。
和oracle的事务管理相同，所有的DDL语句是不能回滚的，并且部分的DDL语句会造成隐
式的提交。
在事务中可以通过定义savepoint，指定回滚事务的一个部分，但是不能指定提交事务
的一个部分。对于复杂的应用，可以定义多个不同的savepoint，满足不同的条件时，回滚
不同的savepoint。需要注意的是，如果定义了相同名字的savepoint，则后面定义的
savepoint会覆盖之前的定义。对于不再需要使用的savepoint，可以通过release savepoint
命令删除savepoint，删除后的savepoint，不能再执行rollback to savepoint命令。
下面我们例子就是模拟回滚事务的一个部分，通过定义savepoint来指定需要回滚的事
务的位置。
mysql> start transaction;
Query OK, 0 rows affected (0.00
sec)
等待
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
+------+------+
1 row in set (37.71 sec)
开始一个事务时，表锁被释放。
对lock方式加的表锁，不能通过rollback
进行回滚。
www.163.com
网易技术部 28
time session_1 session_2
----
----
----
----
----
----
----
----
----
----
----
----
----
----
->
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00
sec)
mysql> delete from tt3 where id =
'2';
Query OK, 1 row affected (0.00
sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
www.163.com
网易技术部 29
mysql> savepoint test;
Query OK, 0 rows affected (0.00
sec)
mysql> delete from tt3 where id =
'3';
Query OK, 1 row affected (0.00
sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback to savepoint test;
Query OK, 0 rows affected (0.00
sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
www.163.com
网易技术部 30
第7 7 7 7章 SQL SQL SQL SQL中的安全问题
7.1 SQL注入简述
SQL Injection攻击具有很大的危害，攻击者可以利用它读取、修改或者删除数据库
内的数据，获取数据库中的用户名和密码等敏感信息，甚至可以 获得数据库管理员的权限。
如果能够再利用SQLServer扩展存储过程和自定义扩展存储过程来执行一些系统命令，攻击
者还可以获得该系统的控制权。而且，SQL Injection 也很难防范。网站管理员无法通过安
装系统补丁或者进行简单的安全配置进行自我保护，一般的防火墙也无法拦截SQL
Injection 攻击。
SQL Injection 原理：
结构化查询语言(SQL)是一种用来和数据库交互的文本语言。SQL Injection 就是利
用某些数据库的外部接口把用户数据插入到实际的数据库操作语言(SQL)当中，从而达到入
侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入 的数据没有进行严格
的过滤，导致非法数据库查询语句的执行。
如下面的用户登陆验证程序：
$sql = "SELECT * FROM user WHERE username='$username' AND
password='$password'";
mysql> commit;
Query OK, 0 rows affected (0.05
sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
2 rows in set (0.00 sec)
www.163.com
网易技术部 31
$result = mysql_db_query($dbname, $sql);
如果我们提交如下url：
http://127.0.0.1/injection/user.php?username=angel' or '1=1
那么就可以成功登陆系统，但是很显然这并不是我们预期的，同样我们也可以利用sql
的注释语句实现sql注入，如下面的例子：
http://127.0.0.1/injection/user.php?username=angel'/*
http://127.0.0.1/injection/user.php?username=angel'%23
这样就把后面的语句给注释掉了！说说这两种提交的不同之处，我们提交的第一句是
利用逻辑运算，第二、三句是根据mysql的特性，mysql支持/*和#两种注释格式，所以我
们提交的时候是把后面的代码注释掉，值得注意的是由于编码问题，在IE地址栏里提交#
会变成空的，所以我们在地址栏提交的时候，应该提交%23，才会变成#，就成功注释了， 这
个比逻辑运算简单得多了。
7.2 开发中可以采取的措施
7.2.1 7.2.1 7.2.1 7.2.1 prepareStatement prepareStatement prepareStatement prepareStatement + + + + Bind-variable Bind-variable Bind-variable Bind-variable
对Java ,Jsp 开发的应用,可以使用 prepareStatement + Bind-variable 来防止
sql注入,另外从PHP 5开始，也在扩展的mysqli中支持prepared statements，所以在使
用这类语言作数据库开发时,强烈建议使用prepareStatement + Bind-variable 来实现,
而尽量不要使用拼接的sql。
7.2.2 7.2.2 7.2.2 7.2.2 使用应用程序提供的转换函数：
很多应用程序接口都提供了对特殊的字符进行转换的函数，恰当的使用这些函数，可
以防止应用程序用户输入使应用程序生成不期望的效果的语句的数值。
MySQL C API：使用mysql_real_escape_string() API调用。
MySQL++ ：使用escape和quote 修饰符。
PHP ：使用mysql_real_escape_string()函数（适用于PHP 4.3.0，之前的版
本请使用mysql_escape_string(), PHP 4.0.3之前的版本请使用addslashes())。从PHP 5
开始,可以使用扩展的mysqli，这是对MYSQL新特性的一个扩展支持，其中的一个优点就是
支持prepared statements。
www.163.com
网易技术部 32
Perl DBI ：使用placeholders或者quote()方法。
Ruby DBI ：使用placeholders或者quote()方法。
Java JDBC ：使用PreparedStatement和 placeholders。
7.2.3 7.2.3 7.2.3 7.2.3 自己定义函数进行校验
如果现有的转换函数仍然不能满足要求，则需要自己编写函数进行输入校验。输入验
证是一个很复杂的问题。输入验证的途径可以分为以下几种：
整理数据使之变得有效；
拒绝已知的非法输入；
只接受已知的合法的输入。
所以如果想要获得最好的安全状态，目前最好的解决办法就是对用户提交或者可能改
变的数据进行简单分类，分别应用正则表达式来对用户提供的输入数据进行严格的检测和验
证。