Mysql prepare预编译防止SQL注入

 

mysql> prepare ins from 'insert into t value (?,?)';

Query OK, 0 rows affected (0.01 sec)

Statement prepared

 

mysql> set @a=1,@b=2;

Query OK, 0 rows affected (0.00 sec)

 

mysql> execute ins using @a,@b;

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from t;

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

| a | b |

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

| 1 | 2 |

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

1 row in set (0.00 sec)

 

mysql>

mysql>

mysql> prepare xy from 'insert into t value(?,?)';

Query OK, 0 rows affected (0.00 sec)

Statement prepared

 

mysql> set @a=11,@b=22;

Query OK, 0 rows affected (0.00 sec)

 

mysql> execute xy using @a,@b;

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from t;

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

| a | b |

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

| 1 | 2 |

| 11 | 22 |

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

2 rows in set (0.00 sec)

 

Pymysql prepare

conn,cur = create_db_conn()
prepare_sql = "prepare 随机字符串 from \'insert into 表名 (字段名1, 字段名2, 字段名3) values (?,?,?)\'"
print(prepare_sql)
cur.execute(prepare_sql)
set_sql = "set @字段名1 =\'{字段值1}\',@字段名2=\'{字段值2}\',@字段名3=\'{字段值3}\'".format(
    ip = "xxx",
    port = "yyy",
    addr = "zzz",
)
print(set_sql)
cur.execute(set_sql)
insert_sql = "execute {随机字符串占位符} using @字段值1,@字段值2,@字段值3".format(sec=data.get('随机字符串的值ps跟prepare一致'))
print(insert_sql)
exc_res = cur.execute(insert_sql)
conn.commit()
if exc_res == 1:
    return {"code":200,"msg":"success"}