Lanms Blog

Mysql事物
Publish: 2019/3/29   

事务 - (transaction / tx)

事务的特点 - ACID 特性

操作方法

begin; -开始事物

commit; - 提交事物 Python 默认是取消自动提交的

rollback; - 回撤操作, 只要操作没有执行 commit 就可以进行回滚操作, 撤回

create table tb_account
(
accid char(4) not null,
uname varchar(20) not null,
balance float default 0
)
insert into tb_account values
('1111', '张明禄', 1200.99),
('2222', '王大锤', 500);
-- 开启一个事物   start transaction
begin;
update tb_account set balance=balance-1000 
where accid='1111';
update tb_account set balance=balance+1000 
where accid='2222';
commit;   -- 提交 才能改变
rollback;  -- 撤销

begin;
delete from  tb_account;  -- 没有commmit 不会删除表
rollback;

SQL 注射攻击

def main():
    config = {
        'host': 'localhost',
        'user': 'root',
        'passwd': '123456',
        'db': 'hrs',
        'charset': 'utf8',
        'cursorclass': pymysql.cursors.DictCursor
    }
    conn = pymysql.connect(**config)
    try:
        uid = input('用户名: ')
        pwd = input('密码: ')
        with conn.cursor() as cursor:
            # 注射攻击的万能密码: a' or '1'='1
            """
            sql = "select 'x' from tb_user where username='%s' \
                     and userpass='%s'" % (uid, pwd)
            if cursor.execute(sql) > 0:
            """
            # cursor.callproc('sp_dept_avg_sal', ())
            # 定义存储过程 / PyMySQL调用存储过程
            if cursor.execute(
                    'select 1 from tb_user where username=%s and userpass=%s',
                    (uid, pwd)):
                print('登录成功, 开始使用系统')
            else:
                print('用户名或密码错误')
    finally:
        conn.close()


← 设计模式简介 Mysql索引-视图-存储 →

Powered by Hexo, Theme designs by @hpcslag.
Style-Framework Tocas-UI designs by @yamioldmel