openresty 防止sql注入

---

openresty 防止sql注入

            

                

                                     

sql 防注入

            

注入示例

sql语句：select * from test where id = ?

拼接语句："sql' ".. id .."'"

# 正常传入参数：id=1
拼接后的sql语句：select * from test where id = '1'

# 传入参数：id = 1' or 1=1#
拼接后的sql语句：select * from test where id = '1' or 1=1#'

# 使用ngx.quote_sql_str：会将id中的单引号转义 ' ==> \'
# 此时拼接后的sql执行报错，达到防注入的目的
拼接后的sql语句：select * from test where id = '1\' or 1=1#'

            

                

                                     

使用示例

            

default.conf

server {
    listen       80;
    server_name  localhost;

    location / {
        root   /usr/local/openresty/nginx/html;
        index  index.html index.htm;
    }

    location /test {
         content_by_lua_block {
             local condition = "1' or 1=1#"

             local sql = "select * from test where id ='"..condition.."'";
             local sql2 = "select * from test where id ='"..ngx.quote_sql_str(condition).."'";

             ngx.say(sql);
             ngx.say(sql2);
         }
    }

    location /test2 {
        content_by_lua_block {
            local mysql = require 'resty.mysql';

            local db, err = mysql:new();
            if not db then 
                ngx.say("mysql创建失败", err);
            end
 
            db:set_timeout(1000);
 
            local res, err, errcode, sqlstate = db:connect({
                host = "172.18.0.61", port = 3306, database = "lihu",
                user = "root", password = "123456"
            });
            if not res then
                ngx.say("连接出错", err, errcode, sqlstate);
            end

            local condition = "1' or 1=1#";
            local sql = "select * from test where id ='"..condition.."'";
            local sql2 = "select * from test where id ='"..ngx.quote_sql_str(condition).."'";

            ngx.say("数据查询:", "select * from test where id ='condition`'");
            res, err, errcode, sqlstate = db:query(sql);
            if not res then
                 ngx.say("数据查询失败", err);
            end

            local cjson = require 'cjson';
            ngx.say("查询结果为:", cjson.encode(res));


            ngx.say("\n数据防注入查询:", "select * from test where id =' ..ngx.quote_sql_str(condition)'");
            res, err, errcode, sqlstate = db:query(sql2);
            if not res then
                 ngx.say("数据查询失败", err);
            end
            
            ngx.say("防注入查询结果为:", cjson.encode(res));

        }
    }

    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/local/openresty/nginx/html;
    }

}

           

创建容器

docker run -it -d --net fixed --ip 172.18.0.62 -p 5003:80 \
-v /Users/huli/lua/openresty/mysql/default.conf:/etc/nginx/conf.d/default.conf \
--name resty-mysql lihu12344/openresty

        

使用测试

# 查看拼接后的sql
huli@hudeMacBook-Pro mysql % curl localhost:5003/test   
select * from test where id ='1' or 1=1#'
select * from test where id =''1\' or 1=1#''


# 注入查询、防注入查询
huli@hudeMacBook-Pro mysql % curl localhost:5003/test2  
数据查询:select * from test where id ='condition`'
查询结果为:[{"name":"gtlx","id":1},{"name":"gtlx","id":3},{"name":"gtlx","id":4},{"name":"gtlx","id":5},{"name":"gtlx","id":6},{"name":"gtlx","id":7},{"name":"gtlx","id":8},{"name":"gtlx","id":9},{"name":"gtlx","id":10},{"name":"gtlx","id":11}]

数据防注入查询:select * from test where id =' ..ngx.quote_sql_str(condition)'
数据查询失败You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1\' or 1=1#''' at line 1
防注入查询结果为:null