如何修复卡点券，卡道具

先说原理思路：

通过ida逆向发现购买商城物品时会触发数据库的taiwan_billing.usp_purchase过程与taiwan_billing.usp_confirm过程。

刷点券的具体原理分两步：

1.当存在大量的同时购买包时，taiwan_billing.usp_purchase过程会产生脏读问题，即cash_cera的值没有变实际下单也没有扣，扣要在taiwan_billing.usp_confirm过程执行完后才会扣，所以读取的一直是cash_cera没扣之前的值，导致v_cera < p_price条件一直为真，导致超量的订单创建。

2.当taiwan_billing.usp_confirm过程判定到v_cur_cera < v_cera时没有进行错误抛出导致程序继续执行，从而执行了数据库语句UPDATE cash_cera SET cera = cera - v_cera, mod_tran = p_tran_id, mod_date = NOW() WHERE account = v_account;。这段数据库语句在cera < v_cera时会导致cera变为负数，而cera字段是无符号的int型，因此负数会表示为这个负数的补码导致点券爆满。

解决方案不是很复杂，由于台服用的数据库版本普遍较低，大部分不支持signal语法，因此可以执行一段错误的sql语句用于中断流程，以便于前面定义的EXIT HANDLER捕获。比如随便写一句insert into test(`a`) values('error');，表test不存在，那么就报错了，后面就不会执行了，第二条就解决了。而第一条不解决会导致物品超发问题，点券没扣物品超发依然是问题。因此要解决这个问题首先要解决并发时下单点券扣除问题。由于cash_cera.cera字段在taiwan_billing.usp_confirm中有引用，因此可以考虑创建一个cash_cera.cera_cold字段用于存储冻结的cera，从而判断是否下单的总点券数超过了现有的点券数，然后在taiwan_billing.sp_purchase中加上当前点券消耗值，taiwan_billing.usp_confirm中扣除点券消耗值，然后再在taiwan_billing.sp_purchase中判断当前点券数-冻结点券数是否小于点券消耗值，从而阻塞下单过程以避免超量订单创建的问题。

 

 

 

解决过程：

 

1.修改taiwan_billing.cash_cera表，新增一个字段：

 

ALTER TABLE `cash_cera` ADD COLUMN `cera_cold` int UNSIGNED NOT NULL DEFAULT 0 AFTER `cera`;

2.修改taiwan_billing.usp_purchase过程：

 

BEGIN

 

DECLARE v_cera INTEGER UNSIGNED;

DECLARE v_err_line INTEGER;

DECLARE v_err_msg VARCHAR(255);

DECLARE v_query VARCHAR(512);

 

DECLARE DF_ERROR01 CONDITION FOR SQLSTATE '90DF1';

DECLARE EXIT HANDLER FOR DF_ERROR01

BEGIN

    ROLLBACK;

 

    INSERT INTO log_error_history (error_id, error_msg, error_query, proc_name, proc_line, query_user, occ_date)

    VALUES (p_out_error, v_err_msg, v_query, 'usp_purchase', v_err_line, USER(), NOW());

END;

 

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

    ROLLBACK;

 

    SET p_out_error = 16;

    INSERT INTO log_error_history (error_id, error_msg, error_query, proc_name, proc_line, query_user, occ_date)

    VALUES(p_out_error, 'Unknow Error.', v_query, 'usp_purchase', v_err_line, USER(), NOW());

 

END;

 

SET v_err_line = 0;

SET v_err_msg = '';

SET v_query = '';

 

SET p_out_tran_id = 0;

SET p_out_error = 0;

 

START TRANSACTION;

 

 

IF (LENGTH(p_account) < 1) OR (p_price <= 0) THEN

    SET p_out_error = 1, v_err_line = 53;

    SET v_err_msg = CONCAT('Invalid Parameter. ', p_account, ',', CAST(p_price AS CHAR));

    insert into test(`a`) values('error');

END IF;

 

 

SET v_cera = NULL;

SET v_query = CONCAT('SELECT cera - cera_cold INTO v_cera FROM cash_cera WHERE account = ''', p_account, '''');

SELECT cera - cera_cold INTO v_cera FROM cash_cera WHERE account = p_account;

IF ISNULL(v_cera) THEN

    SET p_out_error = 17, v_err_line = 63;

    SET v_err_msg = CONCAT('Invalid Account. ', p_account);

    insert into test(`a`) values('error');

END IF;

 

IF (v_cera < p_price) THEN

    SET p_out_error = 3, v_err_line = 69;

    SET v_err_msg = CONCAT('Not Enough Cash. ', CAST(v_cera AS CHAR), ',', CAST(p_price AS CHAR));

    insert into test(`a`) values('error');

END IF;

 

 

SET p_out_tran_id = NULL;

SET v_query = 'INSERT INTO cash_transaction (dummy) VALUES(''P'')';

INSERT INTO cash_transaction (dummy) VALUES('P');

SELECT LAST_INSERT_ID() INTO p_out_tran_id;

IF ISNULL(p_out_tran_id) THEN

    SET p_out_error = 18, v_err_line = 80, p_out_tran_id = 0;

    SET v_err_msg = 'Fail Generate Transaction. Purchase(1)';

    insert into test(`a`) values('error');

END IF;

 

SET v_err_line = 85;

SET v_query = CONCAT('INSERT INTO log_issue_tran_history(tran_id, tran_type, occ_date) VALUES (', CAST(p_out_tran_id AS CHAR), ' 1, NOW())');

INSERT INTO log_transaction_history(tran_id, tran_type, occ_date) VALUES (p_out_tran_id, 1, NOW());

 

SET v_err_line = 89;

SET v_query = CONCAT('INSERT INTO log_purchase_history(tran_id) VALUES(', CAST(p_out_tran_id AS CHAR), ')');

INSERT INTO log_purchase_history(account_id, charac_id, item_id, cera, befor_cera, after_cera, tran_id, tran_state, query_user, occ_date)

VALUES (p_account, p_charac, p_item_id, p_price, v_cera, v_cera - p_price, p_out_tran_id, 1, USER(), NOW());

 

UPDATE cash_cera SET cera_cold = cera_cold + p_price WHERE account = p_account;

 

COMMIT;

 

END

 

 

3.修改taiwan_billing.usp_confirm过程：

 

BEGIN

 

    DECLARE v_account VARCHAR(30);

    DECLARE v_recv_account VARCHAR(30);

    DECLARE v_cera INTEGER;

    DECLARE v_cur_cera INTEGER;

        DECLARE v_cur_cera_cold INTEGER;

    DECLARE v_recv_cera INTEGER;

    DECLARE v_tran_type TINYINT;

    DECLARE v_tran_state TINYINT;

    DECLARE v_expired_time INTEGER;

    DECLARE v_occ_date DATETIME;

    DECLARE v_err_line INTEGER;

    DECLARE v_err_msg VARCHAR(255);

    DECLARE v_query VARCHAR(512);

 

    DECLARE DF_ERROR01 CONDITION FOR SQLSTATE '90DF1';

    DECLARE DF_ERROR02 CONDITION FOR SQLSTATE '90DF2';

 

    DECLARE EXIT HANDLER FOR DF_ERROR01

    BEGIN

        ROLLBACK;

 

        INSERT INTO log_error_history (error_id, error_msg, error_query, proc_name, proc_line, query_user, occ_date)

        VALUES(p_out_error, v_err_msg, v_query, 'usp_confirm', v_err_line, USER(), NOW());

    END;

 

    DECLARE EXIT HANDLER FOR DF_ERROR02

    BEGIN

        COMMIT;

 

        INSERT INTO log_error_history (error_id, error_msg, error_query, proc_name, proc_line, query_user, occ_date)

        VALUES(p_out_error, v_err_msg, v_query, 'usp_confirm', v_err_line, USER(), NOW());

    END;

 

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    BEGIN

        ROLLBACK;

 

        SET p_out_error = 16;

        INSERT INTO log_error_history (error_id, error_msg, error_query, proc_name, proc_line, query_user, occ_date)

        VALUES(p_out_error, 'Unknow Error', v_query, 'usp_confirm', v_err_line, USER(), NOW());

 

        

    END;

 

    SET v_expired_time = 180;

    SET v_err_line = 0;

    SET v_err_msg = '';

    SET v_query = '';

 

    SET p_out_error = 0;

 

    START TRANSACTION;

 

    

    SET v_tran_type = NULL;

    SET v_query = CONCAT('SELECT tran_type INTO v_tran_type FROM log_transaction_history WHERE tran_id = ', CAST(p_tran_id AS CHAR));

        

    SELECT tran_type INTO v_tran_type FROM log_transaction_history WHERE tran_id = p_tran_id;

    IF ISNULL(v_tran_type) THEN

        SET p_out_error = 19, v_err_line = 70;

        SET v_err_msg = CONCAT('Invalid Transaction. ', CAST(p_tran_id AS CHAR));

        insert into test(`a`) values('error');

    END IF;

 

    IF v_tran_type = 1 THEN

        

        SET v_account = NULL;

        SET v_cera = NULL;

        SET v_tran_state = NULL;

        SET v_occ_date = NULL;

 

        SET v_query = CONCAT('SELECT account_id, cera, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date FROM log_purchase_history WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' AND tran_state = 1');

                        

                SELECT account_id, cera, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date

        FROM log_purchase_history

        WHERE tran_id = p_tran_id AND tran_state = 1;

        IF ISNULL(v_account) THEN

            SET p_out_error = 19, v_err_line = 87;

            SET v_err_msg = CONCAT('Invalid Transaction. Purchase,', CAST(p_tran_id AS CHAR));

            insert into test(`a`) values('error');

        END IF;

 

        

        SET v_cur_cera = NULL;

                SET v_cur_cera_cold = NULL;

        SET v_query = CONCAT('SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = ', v_account);

                

        SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = v_account;

                SELECT cera_cold INTO v_cur_cera_cold FROM cash_cera WHERE account = v_account;

                IF ISNULL(v_cur_cera) THEN

            SET p_out_error = 17, v_err_line = 97;

            SET v_err_msg = CONCAT('Invalid Account. Purchase,', v_account);

            insert into test(`a`) values('error');

        END IF;

 

        IF v_cur_cera < v_cera THEN

            SET p_out_error = 3, v_err_line = 103;

            SET v_err_msg = CONCAT('Not Enough Cash. Purchase,', CAST(v_cur_cera AS CHAR), ',', CAST(v_cera AS CHAR));

                        insert into test(`a`) values('error');

        END IF;

                

                IF v_cur_cera_cold < v_cera THEN

                        UPDATE cera_cold SET cera_cold = v_cera WHERE account = v_account;

        END IF;

        

        IF ( TIME_TO_SEC(timediff(NOW(),v_occ_date)) > v_expired_time ) THEN

SET v_err_line = 110;

            SET v_query = CONCAT('UPDATE log_purchase_history SET tran_state = 4 WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' tran_state = 1');

                        

            UPDATE log_purchase_history SET tran_state = 4 WHERE tran_id = p_tran_id AND tran_state = 1;

 

            SET p_out_error = 20, v_err_line = 114;

            SET v_err_msg = CONCAT('Expired transaction. Purchase,', CAST(p_tran_id AS CHAR), ',', DATE_FORMAT(v_occ_date, GET_FORMAT(DATETIME,'INTERNAL')));

            insert into test(`a`) values('error');

        END IF;

 

SET v_err_line = 119;

        SET v_query = CONCAT('UPDATE cash_cera SET cera = cera - v_cera, mod_tran = ', CAST(p_tran_id AS CHAR), ', mod_date = NOW() WHERE account = ''', v_account, '''');

                

        UPDATE cash_cera SET cera = cera - v_cera, cera_cold = cera_cold - v_cera, mod_tran = p_tran_id, mod_date = NOW() WHERE account = v_account;

                SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = v_account;

SET v_err_line = 123;

        SET v_query = CONCAT('UPDATE log_purchase_history SET tran_state = 2 WHERE tran_id = ', CAST(p_tran_id AS CHAR), '  AND tran_state = 1');

        UPDATE log_purchase_history SET tran_state = 2,befor_cera = v_cur_cera,after_cera = v_cur_cera - v_cera WHERE tran_id = p_tran_id AND tran_state = 1;

 

    ELSEIF v_tran_type = 2 THEN

        

        SET v_account = NULL;

        SET v_cera = NULL;

        SET v_tran_state = NULL;

        SET v_occ_date = NULL;

 

        SET v_query = CONCAT('SELECT account_id, cera, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date FROM log_recharge_history WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' AND tran_state = 1');

        SELECT account_id, cera, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date

        FROM log_recharge_history

        WHERE tran_id = p_tran_id AND tran_state = 1;

        IF ISNULL(v_account) THEN

            SET p_out_error = 19, v_err_line = 139;

            SET v_err_msg = CONCAT('Invalid Transaction. Recharge,', CAST(p_tran_id AS CHAR));

            insert into test(`a`) values('error');

        END IF;

 

        

        SET v_cur_cera = NULL;

        SET v_query = CONCAT('SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = ', v_account);

        SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = v_account;

        IF ISNULL(v_cur_cera) THEN

            SET p_out_error = 17, v_err_line = 149;

            SET v_err_msg = CONCAT('Invalid Account. Recharge,', v_account);

            insert into test(`a`) values('error');

        END IF;

 

        

        IF ( TIME_TO_SEC(timediff(NOW(),v_occ_date)) > v_expired_time ) THEN

SET v_err_line = 156;

            SET v_query = CONCAT('UPDATE log_recharge_history SET tran_state = 4 WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' tran_state = 1');

            UPDATE log_recharge_history SET tran_state = 4 WHERE tran_id = p_tran_id AND tran_state = 1;

 

            SET p_out_error = 20, v_err_line = 160;

            SET v_err_msg = CONCAT('Expired transaction. Recharge,', CAST(p_tran_id AS CHAR), ',', DATE_FORMAT(v_occ_date, GET_FORMAT(DATETIME,'INTERNAL')));

            insert into test(`a`) values('error');

        END IF;

 

SET v_err_line = 165;

        SET v_query = CONCAT('UPDATE cash_cera SET cera = cera + v_cera, mod_tran = ', CAST(p_tran_id AS CHAR), ', mod_date = NOW() WHERE account = ''', v_account, '''');

        UPDATE cash_cera SET cera = cera + v_cera, mod_tran = p_tran_id, mod_date = NOW() WHERE account = v_account;

 

SET v_err_line = 169;

        SET v_query = CONCAT('UPDATE log_recharge_history SET tran_state = 2 WHERE tran_id = ', CAST(p_tran_id AS CHAR), '  AND tran_state = 1');

        UPDATE log_recharge_history SET tran_state = 2 WHERE tran_id = p_tran_id AND tran_state = 1;

 

    ELSEIF v_tran_type = 3 THEN

        

        SET v_account = NULL;

        SET v_recv_account = NULL;

        SET v_cera = NULL;

        SET v_tran_state = NULL;

        SET v_occ_date = NULL;

 

        SET v_query = CONCAT('SELECT account_id, cera, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date FROM log_gift_history WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' AND tran_state = 1');

        SELECT send_account_id, recv_account_id, cera, tran_state, occ_date INTO v_account, v_recv_account, v_cera, v_tran_state, v_occ_date

        FROM log_gift_history

        WHERE tran_id = p_tran_id AND tran_state = 1;

        IF ISNULL(v_account) OR ISNULL(v_recv_account) THEN

            SET p_out_error = 19, v_err_line = 186;

            SET v_err_msg = CONCAT('Invalid Transaction. Gift,', CAST(p_tran_id AS CHAR));

            insert into test(`a`) values('error');

        END IF;

        

 

        SET v_cur_cera = NULL;

        SET v_query = CONCAT('SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = ', v_account);

        SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = v_account;

        IF ISNULL(v_cur_cera) THEN

            SET p_out_error = 17, v_err_line = 196;

            SET v_err_msg = CONCAT('Invalid Account. Gift,', v_account);

            insert into test(`a`) values('error');

        END IF;

 

        IF v_cur_cera < v_cera THEN

            SET p_out_error = 3, v_err_line = 202;

            SET v_err_msg = CONCAT('Not Enough Cash. Gift,', CAST(v_cur_cera AS CHAR), ',', CAST(v_cera AS CHAR));

            insert into test(`a`) values('error');

        END IF;

 

        

        IF ( TIME_TO_SEC(timediff(NOW(),v_occ_date)) > v_expired_time ) THEN

SET v_err_line = 209;

            SET v_query = CONCAT('UPDATE log_gift_history SET tran_state = 4 WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' tran_state = 1');

            UPDATE log_gift_history SET tran_state = 4 WHERE tran_id = p_tran_id AND tran_state = 1;

 

            SET p_out_error = 20, v_err_line = 213;

            SET v_err_msg = CONCAT('Expired transaction. Gift,', CAST(p_tran_id AS CHAR), ',', DATE_FORMAT(v_occ_date, GET_FORMAT(DATETIME,'INTERNAL')));

            insert into test(`a`) values('error');

        END IF;

 

SET v_err_line = 218;

        SET v_query = CONCAT('UPDATE cash_cera SET cera = cera - v_cera, mod_tran = ', CAST(p_tran_id AS CHAR), ', mod_date = NOW() WHERE account = ''', v_account, '''');

        UPDATE cash_cera SET cera = cera - v_cera, mod_tran = p_tran_id, mod_date = NOW() WHERE account = v_account;

 

SET v_err_line = 222;

        SET v_query = CONCAT('UPDATE cash_cera SET cera = cera + v_cera, mod_tran = ', CAST(p_tran_id AS CHAR), ', mod_date = NOW() WHERE account = ''', v_account, '''');

        UPDATE cash_cera SET cera = cera + v_cera, mod_tran = p_tran_id, mod_date = NOW() WHERE account = v_recv_account;

 

SET v_err_line = 226;

        SET v_query = CONCAT('UPDATE log_gift_history SET tran_state = 2 WHERE tran_id = ', CAST(p_tran_id AS CHAR), '  AND tran_state = 1');

        UPDATE log_gift_history SET tran_state = 2 WHERE tran_id = p_tran_id AND tran_state = 1;

 

    ELSEIF v_tran_type = 4 THEN

                

        SET v_account = NULL;

        SET v_cera = NULL;

        SET v_tran_state = NULL;

        SET v_occ_date = NULL;

 

        SET v_query = CONCAT('SELECT account_id, cera, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date FROM log_purchase_history WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' AND tran_state = 1');

        SELECT account_id, amount, tran_state, occ_date INTO v_account, v_cera, v_tran_state, v_occ_date

        FROM log_refund_history

        WHERE tran_id = p_tran_id AND tran_state = 1;

        IF ISNULL(v_account) THEN 

            SET p_out_error = 22, v_err_line = 242;

            SET v_err_msg = CONCAT('Invalid Transaction. Refund,', CAST(p_tran_id AS CHAR));

            insert into test(`a`) values('error');

        END IF;

 

                

        SET v_cur_cera = NULL;

        SET v_query = CONCAT('SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = ', v_account);

        SELECT cera INTO v_cur_cera FROM cash_cera WHERE account = v_account;

        IF ISNULL(v_cur_cera) THEN

            SET p_out_error = 17, v_err_line = 252;

            SET v_err_msg = CONCAT('Invalid Account. Refund,', v_account);

            insert into test(`a`) values('error');

        END IF;

 

        IF v_cur_cera < v_cera THEN

            SET p_out_error = 3, v_err_line = 258;

            SET v_err_msg = CONCAT('Not Enough Cash. Refund,', CAST(v_cur_cera AS CHAR), ',', CAST(v_cera AS CHAR));

            insert into test(`a`) values('error');

        END IF;

 

                

        IF ( TIME_TO_SEC(timediff(NOW(),v_occ_date)) > v_expired_time ) THEN

SET v_err_line = 265;

            SET v_query = CONCAT('UPDATE log_purchase_history SET tran_state = 4 WHERE tran_id = ', CAST(p_tran_id AS CHAR), ' tran_state = 1');

            UPDATE log_refund_history SET tran_state = 4 WHERE tran_id = p_tran_id AND tran_state = 1;

 

            SET p_out_error = 20, v_err_line = 269;

            SET v_err_msg = CONCAT('Expired transaction. Refund,', CAST(p_tran_id AS CHAR), ',', DATE_FORMAT(v_occ_date, GET_FORMAT(DATETIME,'INTERNAL')));

            insert into test(`a`) values('error');

        END IF;

 

SET v_err_line = 274;

        SET v_query = CONCAT('UPDATE cash_cera SET cera = cera - v_cera, mod_tran = ', CAST(p_tran_id AS CHAR), ', mod_date = NOW() WHERE account = ''', v_account, '''');

        UPDATE cash_cera SET cera = cera - v_cera, mod_tran = p_tran_id, mod_date = NOW() WHERE account = v_account;

 

SET v_err_line = 278;

        SET v_query = CONCAT('UPDATE log_refund_history SET tran_state = 2 WHERE tran_id = ', CAST(p_tran_id AS CHAR), '  AND tran_state = 1');

        UPDATE log_refund_history SET tran_state = 2 WHERE tran_id = p_tran_id AND tran_state = 1;

 

    END IF;

 

    COMMIT;

 

END

4.修改事务等级及存储方式，避免再现其它并发问题（可忽略）

 

ALTER TABLE `cash_cera` ENGINE=InnoDB;

ALTER TABLE `cash_cera_point` ENGINE=InnoDB;

ALTER TABLE `cash_transaction` ENGINE=InnoDB;

ALTER TABLE `cs_table2` ENGINE=InnoDB;

ALTER TABLE `log_error_history` ENGINE=InnoDB;

ALTER TABLE `log_gift_history` ENGINE=InnoDB;

ALTER TABLE `log_item_refund_history` ENGINE=InnoDB;

ALTER TABLE `log_point_history` ENGINE=InnoDB;

ALTER TABLE `log_purchase_history` ENGINE=InnoDB;

ALTER TABLE `log_recharge_history` ENGINE=InnoDB;

ALTER TABLE `log_refund_history` ENGINE=InnoDB;

ALTER TABLE `log_transaction_history` ENGINE=InnoDB;

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;