openresty / lua-resty-mysql Goto Github PK
View Code? Open in Web Editor NEWNonblocking Lua MySQL driver library for ngx_lua or OpenResty
Nonblocking Lua MySQL driver library for ngx_lua or OpenResty
I get "lua tcp socket read timed out" errors every so often and wanted to see if maybe if my syntax is not used correctly for lua mysql before i assume a bug exists? it's designed to use mysql to return docroot for each hosting customer based on what subdomain they use.
detailed error:
2015/02/21 20:49:19 [error] 22343#0: *281222 lua tcp socket read timed out, client: x.x.x.x, server: *.mydomain.com, request: "GET /administration/ping?=1424573359691 HTTP/1.1", host: "jshah4722b.mydomain.com", referrer: "https://jshah4722b.mydomain.com/patients/index/task:edit/patient_id:3630"
2015/02/21 20:49:19 [error] 22343#0: *281222 [lua] rewrite_by_lua:78: MySQL bad result: failed to receive packet header: timeout: nil: nil., client: x.x.x.x, server: *.mydomain.com, request: "GET /administration/ping?=1424573359691 HTTP/1.1", host: "jshah4722b.mydomain.com", referrer: "https://jshah4722b.mydomain.com/patients/index/task:edit/patient_id:3630"
here's what i have:
it's called using this method:
server {
server_name *.mydomain.com;
set $httphost $http_host; # create and initialize var
set $docroot "";
include /etc/nginx/lua.conf;
# docroot is defined from above LUA script
root $docroot/app/webroot/;
/etc/nginx/lua.conf contents:
lua_socket_log_errors on;
lua_check_client_abort on;
lua_socket_read_timeout 120s;
rewrite_by_lua '
-- make sure http host is defined
if not ngx.var.httphost then
ngx.log(ngx.ERR,"ERROR - no httphost defined")
return
end
local host=ngx.var.httphost
-- make sure more than 1 dot in the HOST name
local _, pcount = string.gsub(host, "%.", "")
if pcount < 2 then
ngx.log(ngx.ERR,"ERROR HOST has 1 dot! -> " .. host)
end
local client,tld=host:match"([^.]*).(.*)"
if not client then
ngx.log(ngx.ERR,"ERROR - no client defined")
return
end
local mysql = require "resty.mysql"
local db, err = mysql:new()
if not db then
ngx.log(ngx.ERR,"MySQL failed to initiate: ", err)
return
end
db:set_timeout(10000) -- 10 sec
local ok, err2, errno, sqlstate = db:connect
{
host = "10.80.18.211",
port = 3306,
database = "onetouch_admin",
user = "docroot",
password = "0netouch3MR",
max_packet_size = 1024 * 1024 * 7
}
if not ok then
ngx.log(ngx.ERR,"MySQL failed to connect: ", err2, ": ", errno, " ", sqlstate)
return
end
local hname = ngx.unescape_uri(client)
local quoted_name = ngx.quote_sql_str(hname)
local sql = "SELECT `version` FROM `users` WHERE `customer` =" .. quoted_name
local result,err,errno,sqlstate = db:query(sql,1)
if not result then
ngx.log(ngx.ERR,"MySQL bad result: ", err, ": ", errno, ": ", sqlstate, ".")
return
end
if result[1] == nil then
-- ngx.log(ngx.ERR,"MySQL - no docroot was returned for host:",host)
ngx.var.docroot = "/var/www/production"
return
end
local ok3, err3 = db:set_keepalive(15000, 400)
if not ok3 then
ngx.log(ngx.ERR, "failed to set keepalive: ", err3)
end
ngx.var.docroot = result[1].version
';
this part in the readme is a bit unclear to me, does this mean we can't do mysql = require "resty.mysql"
at the module level or we can't do mysql::new()
at the module level?
The resty.mysql object instance cannot be stored in a Lua variable at the Lua module level, because it will then be shared by all the concurrent requests handled by the same nginx worker process (see http://wiki.nginx.org/HttpLuaModule#Data_Sharing_within_an_Nginx_Worker ) and result in bad race conditions when concurrent requests are trying to use the same resty.mysql instance. You should always initiate resty.mysql objects in function local variables or in the ngx.ctx table. These places all have their own data copies for each request.
When i tried the query method as the readme said:
res,err,errcode,sqlstate=db:query("select * from account where id="..id..";")
The correct query was OK.
And to test the condition of bad result("empty set"),i use an id that does not exits in the MySQL table.However,unexpectedly,the if statement following the query:
if not res then
never excuted,which means res is not nil......
And i tried if not res[1] then
,it works as i expected(Though err errcode sqlstate were all nil )
I want to know what's wrong with it :)
error:
"Incorrect datetime value: 'Tue Jan 12 18:31:10 2016' for column 'createTime' at row 1"
table:
CREATE TABLE jctx_group_members
(
id
int(11) unsigned NOT NULL AUTO_INCREMENT,
groupId
int(20) unsigned NOT NULL,
uid
int(11) NOT NULL,
createTime
datetime NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
code:
local current_time = os.date()
local sql_create = "insert into jctx_group_info"
.."(creatorUid, groupName, createTime) values ("
..groupCreator..",\'"..groupName.."\',\'"..current_time.."\')"
local res, err, errno, sqlstate = self.database:query(sql_create)
Hello,
at first sorry for this question... I am newbie in Lua.
When I use cjson.encode(res), I'll get something like this:
[{
"id": 1,
"col1": "value1",
"col2": "value2",
"col3": null
}]
Can you tell me how can I get value of specific column?
I tried res[0].col2, res[0][col2], res[0]["col2"], but it doesn't work.
del
The function connect will create a new mysql connection or select a free connection from pool! If there are too many connection using, I don't want it to return immediately until another connection is released! How to do it?
Is anyone else seeing aborted mysql connections when using this? We are using percona mysql, and I am calling the db:close() as shown below.
local ok, err = db:close()
if not ok then
ngx.log(ngx.DEBUG, 'failed to close: ', err)
end
I found lua-nginx-websocket
in Debian's repository that is maintained by Laurent Arnoud which refers to lua-resty-websocket. Just wondering if there is a way to install packages like lua-resty-mysql/redis/etc
with aptitude
or luarocks
?
Hello, When I query an statement like update user set username='bbbbbb' where id=2;
, will get result like (as json){"insert_id":0,"server_status":2,"warning_count":0,"affected_rows":1,"message":"(Rows matched: 1 Changed: 0 Warnings: 0"}
. As you see, the first character of message
is (
, which is a little wired. When in mysql client shell, it's like
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL ping method/query could let us know in advance before executing a query if the server is reachable and in good state. The feature request is to add this ping method on a db object which returns err to say whether it was success or failure.
我是用 lua-resty-mysql 从mysql取数据, 每次改sql 都需要重新reload,比较麻烦, 請問有什么配置可以不需要reload吗。
ubuntu server 14.04 (512M, Aliyun ECS),lua-resty-mysql (0.17) and openresty (1.11.2.2). When I attempt to insert about 100K tiems, the database failed about 7k items, some are due to too many connections
, the other are out of memory.
I set the pool size to 50 in lua layer. and use the default settting in mysql server layer. I also wrap the query logic to a module and use it via require:
local driver = require "mysql"
local CONNECT_TABLE = {
host = "127.0.0.1",
port = 3306,
database = "test",
user = 'root',
password = '', }
local CONNECT_TIMEOUT = 1000
local IDLE_TIMEOUT = 10000
local POOL_SIZE = 50
local function query(statement, rows)
-- utils.loger(statement)
local db, err = driver:new()
if not db then
return nil, err
end
db:set_timeout(CONNECT_TIMEOUT)
local res, err, errno, sqlstate = db:connect(CONNECT_TABLE)
if not res then
return nil, err, errno, sqlstate
end
res, err, errno, sqlstate = db:query(statement, rows)
if res ~= nil then
local ok, err = db:set_keepalive(IDLE_TIMEOUT, POOL_SIZE)
if not ok then
return nil, err
end
end
return res, err, errno, sqlstate
end
return query
First of all, cosocket
is cool :) Thanks for the excellent work @agentzh and other contributors.
I could never imagine writing an async mysql / postgres driver in lua within 1k lines of codes.
Currently when we fetch a result set, we call db:query
, which returns all the rows
:
local row = _parse_row_data_packet(packet, cols, compact)
i = i + 1
rows[i] = row
-- ......
return rows
In many (or at lease some) cases however we don't need to keep all records in memory.
It would be nice if we could do something like this (take json output as an example):
function row_event_hook(row)
-- 'row' is returned by '_parse_row_data_packet' function, a lua table
-- you can do something like ngx.say(cjson.encode(row)) or do whatever you want
ngx.say(cjson.encode(row))
-- the above is just an arbitrary example without details like commas etc.
end
ngx.say('[')
-- if row_event_hook is provided, records are not saved in the memory by default
-- if row_event_hook is not provided, return the array of records as usual
db:query('SELECT foo FROM bar', row_event_hook)
ngx.say(']')
I do understand it's probably not worth it in the above example because it was mentioned in another issue that ngx.say
doesn't buffer (it would be nice to have ngx.write_to_buffer
function :) ). But there are other scenarios where a row_event_hook
does make sense to prevent the request cycle from unnecessary memory allocations. Like writing BLOB data (blob_data_event_hook) through some IO stream (or HTTP response) etc.
So to wrap up, it would be nice to have some event hook support since we can potentially save some memory per request cycle, which is very important for a high concurrency web api / app.
Any suggestions / comments welcome.
local function _get_byte8(data, i)
local a, b, c, d, e, f, g, h = strbyte(data, i, i + 7)
-- XXX workaround for the lack of 64-bit support in bitop:
local lo = bor(a, lshift(b, 8), lshift(c, 16), lshift(d, 24))
local hi = bor(e, lshift(f, 8), lshift(g, 16), lshift(h, 24))
return lo + hi * 4294967296, i + 8
end
current resty.mysql code is above when read 8byte integer.
but LuaJIT use a boxed int64 for 64bit ineger support, so the "lo + hi * 4294967296" upper is 2^53, exceed will lost some bits
https://travis-ci.org/chipitsine/lua-resty-mysql/jobs/134985756#L1214
test complains on missing world.City, where can I take sql script for that table ?
bytes, err = db:send_query(query)
res, err, errcode, sqlstate = db:read_result()
res, err, errcode, sqlstate = db:read_result(nrows)
res, err, errcode, sqlstate = db:query(query)
res, err, errcode, sqlstate = db:query(query, nrows)
db:query() may return 2 values, if we access the 3rd return value, we may get nil, but we expect errcode
.
I'm getting "cannot be reused in the current connection state: 2" error when trying to set_keepalive() mysql connection after concatenated SQL-query (multiple sql queries in one request) sent.
I want to insert 3 SQL statements are inserted into the 3 table, but the performance is too low, I was so written, how do I do to make the performance higher?
the code like this:
example1:
local db,err = mysql:new()
local ok,err,errno,sqlstate = db:connect(dbconf)
if not ok then
ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
return
end
res, err, errno, sqlstate = db:query("BEGIN;")
if not res then
ngx.log(ngx.ERR, "bad result #", ": ", err, ": ", errno, ": ", sqlstate, ".")
return
end
res, err, errno, sqlstate = db:query("insert1;")
if not res then
ngx.log(ngx.ERR, "bad result #", ": ", err, ": ", errno, ": ", sqlstate, ".")
return
end
res, err, errno, sqlstate = db:query("insert2;")
if not res then
ngx.log(ngx.ERR, "bad result #", ": ", err, ": ", errno, ": ", sqlstate, ".")
return
end
res, err, errno, sqlstate = db:query("insert3;")
if not res then
ngx.log(ngx.ERR, "bad result #", ": ", err, ": ", errno, ": ", sqlstate, ".")
return
end
res, err, errno, sqlstate = db:query("COMMIT;")
if not res then
ngx.log(ngx.ERR, "bad result #", ": ", err, ": ", errno, ": ", sqlstate, ".")
return
end
local ok, err = db:set_keepalive(10000, 1000)
if not ok then
ngx.log(ngx.ERR,"failed to set keepalive: ",err)
--ngx.exit(500)
end
the performance of examnple1 like that:
example2:
local db,err = mysql:new()
local ok,err,errno,sqlstate = db:connect(dbconf)
if not ok then
ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
return
end
res, err, errno, sqlstate = db:query("START TRANSACTION;insert sql1;insert sql2;insert sql3;COMMIT;")
if not res then
ngx.log(ngx.ERR, "bad result #", ": ", err, ": ", errno, ": ", sqlstate, ".")
return
end
local ok, err = db:set_keepalive(10000, 1000)
if not ok then
ngx.log(ngx.ERR,"failed to set keepalive: ",err)
--ngx.exit(500)
end
the performance of example2 like that:
./wrk -t10 -c 100 -d1m "url"
How can i apply the transaction to improve the performance? The way of appling the transcation is right?But lack the ROLLBACK ,what's the right way?
能给一个把mysql封装成一个模块的例子吗,传一个sql语句,返回结果的。自己测试了很久达不到那个吞吐量,又不知道问题出在哪里
Do we have PDO style query in lua-resty-mysql
The query and params can be sent separately rather than a string and it takes care of sql injection etc.
hi, @agentzh
It's just a small & quick question.
Why you commented line below? ;-) Protocol::MYSQL_TYPE_LONGLONG should also be number?
-- converters[0x08] = tonumber -- long long
where can I get that cert/key ?
https://github.com/openresty/lua-resty-mysql/blob/master/t/ssl.t#L96
should I generate it on the fly ?
why not call sock::close when read timeout like redis.lua did?
In redis.lua,the connection pool was destroyed when read timeout happens
SHA-1: c75af8fe7f71ca05e4bd3ccb3923f6a81d62a67a
bugfix: the memcached connection might enter a bad state when read timeout happens because ngx_lua's cosocket reading calls no longer automatically close the connection in this case.
so my question is why did not treat this as a bug in mysql.lua
thanks
local ok, err, errno, sqlstate = db:connect{
host = "my.mysql.domain.com",
port = 3306,
database = "test",
user = "test",
password = "test",
max_packet_size = 1024 * 1024 }
in development env.,host is "127.0.0.1", it is ok.
in product env. host is similar domain name,return is failed to connect ...
I add below code after connect, but it always return zero on every request.
local times, err = db:get_reused_times()
if not times then
ngx.say("failed to get get_reused_times: ", err)
return
end
ngx.say("connection reused ", times," times.")
there is a case that when i use this lib to connect mysql, a warning which says [unkown charset num:0, charset will be set as utf8] will occur on database server. so i think that connect function needs a parameter for charset encoding. i saw agenth's comment which said that u don't like to set names on client and there is default charset on server though.....just an advice~
In PHP you have 2 part, execute query and then fetch the result one by one, this is save resources and very fast.
I am comparing fetching 100000records and php is very slow if i buffer the result first then have loop to process the data. But if we use PHP way, execute query and then fetch one by one, its 10x faster.
I hope there is solution (i think its easy).
PHP CODE
$xmtime = microtime(TRUE);
$res = queryi("select * from transaksi limit 100000");
echo '<br>';
echo microtime(TRUE)-$xmtime;
echo 'Seconds';
echo "<hr>";
$xmtime = microtime(TRUE);
$ftotalprice=4;
$fjumlah=3;
$fidmtr=1;
while ($r=$res->fetch_row()) {
echo $r[$ftotalprice],$r[$fjumlah],$r[$fidmtr];
}
echo '<br>';
echo microtime(TRUE)-$xmtime;
echo 'Seconds';
echo "<hr>";
MOONSCRIPT
x=os.clock()
res = queryi("select * from transaksi limit 100000",10000)
raw '<br>'
raw os.clock()-x
raw 'Seconds'
raw "<hr>"
x=os.clock()
ftotalprice=5
fjumlah=4
fidmtr=2
for r in *res
raw r[ftotalprice],r[fjumlah],r[fidmtr]
raw '<br>'
raw os.clock()-x
raw 'Seconds'
raw "<hr>"
It would be great if this library could support cursors. Is there a plan to add this or is there a reason not to do it?
luasql has this support but of course it's a blocking library so it's not as useful with openresty.
Thanks for lua-resty-mysql -- it's a great libary!
Related issue: #24
765 local function read_result(self, est_nrows)
766 if self.state ~= STATE_COMMAND_SENT then
767 return nil, "cannot read result in the current context: " .. self.state
768 end
bug???
resty/mysql.lua:794: attempt to concatenate field 'state' (a nil value)
When I query data from DB, I found I got string for DECIMAL from online DB but number from test DB. I checked the code, I think it maybe do not handle 0x00(FIELD_TYPE_DECIMAL). Could you tell me why? Thanks!
Is it possible to get the last inserted row id without making an extra query?
In perl DBI you can do i.e. $db->{mysql_insertid}
Aliyun RDS server require mysql_host using domain name. When I set
"mysql_host = xxxxxxxxxx.mysql.rds.aliyuncs.com", I found can not connect to mysql.
Error info: failed to connect: no resolver defined to resolve "xxxxxxxxxxxx.mysql.rds.aliyuncs.com".
How can I do about it.
In _parse_row_data_packet(), it does not convert value of column contains null(ngx.null). It causes compatibility issue with generic lua modules like JSON. Could you explain why it returns ngx.null instead of nil? I can fix it on my needs, but I hope that it will be fixed officially if there's no any other issues related on that change.
Hello,
I get a strange error from time to time:
lua entry thread aborted: runtime error: /resty/mysql.lua:63: attempt to call local 'new_tab' (a table value)
stack traceback:
coroutine 0:
[C]: in function 'require'
And after that this error:
2017/03/29 22:05:16 [error] 65691#0: *7148887 lua entry thread aborted: runtime error: search16.lua:1877: loop or previous error loading module 'resty.mysql'
stack traceback:
coroutine 0:
[C]: in function 'require'
Of cource I load module only one time. I tried to load module at the beginning of the script - there is no difference. This error happens.
I use mysql.lua to work with sphinx db. Could you please help me to understand what I'm doing wrong?
Hello!
I have 2 identical virtual servers. When I have connected in lua script to local MySQL at first of these servers - all OK. But when I try to do this at second server my browser broke connection to local MySQL. If I try to connect from second server (via lua) to first MySQL I get this error:
"failed to connect: Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts': 1129 nil"
I don't understand what difference inter my servers.....I have already copy my.cnf, test.lua script, etc from first server to second but it has no effect. One server works, another - not work.....Mystic....
I'd appreciate any help!
如何修改编码呢
local mysql = require "resty.mysql"
local db,err = mysql:new()
if not db then
ngx.say("failed to instantiate mysql: ",err)
return
end
db:set_timeout(1000)
local ok,err,errno,sqlstate = db:connect{
host = "127.0.0.1",
port = 3306,
database = "db_blog",
user = "root",
password = "admin",
max_package_size = 1024,
charset = "utf-8"
}
if not ok then
ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
return
end
res,err,errno,sqlstate = db:query("select * from wp_terms")
if not res then
ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
return
end
local cjson = require "cjson"
ngx.say(cjson.encode(res))
结果
[{"term_group":"0","name":"????","term_id":"1","slug":"help"},{"term_group":"0","name":"??","term_id":"2","slug":"novel"},{"term_group":"0","name":"??","term_id":"3","slug":"service"},{"term_group":"0","name":"main_menu","term_id":"4","slug":"main_menu"},{"term_group":"0","name":"??","term_id":"5","slug":"about"},{"term_group":"0","name":"????","term_id":"6","slug":"info"},{"term_group":"0","name":"????","term_id":"7","slug":"notice"},{"term_group":"0","name":"????","term_id":"8","slug":"job"},{"term_group":"0","name":"????","term_id":"9","slug":"cooperate"},{"term_group":"0","name":"????","term_id":"10","slug":"%e5%bc%82%e5%b8%b8%e5%a4%84%e7%90%86"}]
Such like select filename,md5,dfspath from dfs_mapping where uri like '%player2116702552%'
,i found it not working.
First, thanks your great job!
My question: It's not safe, how to escape data ?
local sql = "INSERT INTO `mytable` (`title`) VALUES (`" .. title .. "`)"
*454 lua entry thread aborted: runtime error: /usr/local/openresty/lualib/resty/mysql.lua:300: attempt to perform arithmetic on local 'len' (a boolean value) stack traceback: coroutine 0: /usr/local/openresty/lualib/resty/mysql.lua: in function '_from_length_coded_str' /usr/local/openresty/lualib/resty/mysql.lua:423: in function '_parse_row_data_packet' /usr/local/openresty/lualib/resty/mysql.lua:870: in function 'query'
This error appeared after we migrate to MYSQL 5.7.14, in previous versions everything was ok.
currently we use:
openresty/1.11.2.1
lua-resty-mysql-0.16
Like an example,is each http request creates a new connection, or to check is have a pool. If have a pool,use it .if not create a pool
local ok, err, errno, sqlstate = db:connect{
host = "127.0.0.1",
port = 3306,
database = "ngx_test",
user = "ngx_test",
password = "ngx_test",
max_packet_size = 1024 * 1024 }
I suggest to add tonumber converter NEWDECIMAL field type (0xf6). Otherwise results of aggregate functions (such as SUM) are treated as strings.
Do not support transaction
Right?
My server chatsert is utf8mb4, and also i query "set names 'utf8mb4'" after connect
Hi,
first, thanks for all this work on openresty!, it is very useful :). And sorry for the large comment :)
I'm considering using it for replacing some parts of our REST API, and could implement it in short time, with straightforward code, and that was able to outperform by a significant margin our current implementation that do have a lot of quirks on the code to gain speed, so: great :)
It would be even better, for our use case, if we could use prepared statements instead of building the query string each time. We tend to have a small number of different queries, that runs thousands time per second in response to client requests. In that scenario server-side prepared statements are great:
I implemented support for prepare/execute/close prepared statements, that implementation itself was not difficult, mainly a new function to parse rows, and set it or the text-based one as callback depending if the response is for a textual query or for a prepared_statement execute() one.
Would you be interesting in adding this to lua-resty-mysql ? I can work on this and prepare a patch
How the API for this should looks like?. For testing I used:
st = db:prepare("select . from .. where col1 = ? and col2 = ?")
result = st:call("Value1", "Value2")
st:close()
And this is the important one: I don't want to prepare/execute/close the statement each time. That is likely more resource intensive than just sending a plain text query. Instead, want to somehow keep with each mysql connection the set of prepared statements on it, so we could do things like:
local st = db:prepared("st_1")
if not st then
st = db:prepare("st_1", "some query....")
end
Mysql assign an id to each prepared statement, that you use for calling it. And that id is per-connection, so statement "st_1" could be 7 on one connection, and 3 on another one.
I failed to find an easy way to "attach" info to a mysql connection. I'm right that probably need to be actually attached to the cosocket object?. I couldn’t find any obvious way to do that neither, as new tables are created and returned each time, no matter the socket is comming back from the pool. I tried to cheat and use the c-data in the cosocket as key in a weak table, but that didn't work , although not really sure I was using it right.
Without 3) , adding prepared statement is not that useful (might be for security, as you can't forget to escape a parameter, or might be preferable too if the result set is big enough than the cost of prepare/execute/close is less than the speed gained by using binary rather than textual protocol for results)
Cheating, just for testing, and using only 1 prepared statement, and assuming it will be assigned id "1" on all sessions by mysql, I was able to test how well this behave, and by reusing the prepared statement for a simple query that returns 1 row, the throughput increased significantly (>50% ), while mysql server used less CPU.
So, what's your opinion?, would be interested?, if so, how you think the API should looks like?. It is point 3) possible with current cosocket/mysql design?
春哥,我用直接用中文了.大环境:win10 64 + openresty-1.9.7.4-win32.
我自己做了一个基于lua-resty-mysql查询的包装函数query, 主要是为了方便,不用去管连接的建立和释放.比如在content_by_lua_block中这样用:
local query=require"mylib.query"
local res1=query('SELET * FROM table1')
local res2=query('SELET name,age FROM table1 WHERE id=1')
这个函数在以下情况都表现正确:
1.只提交了一个sql 查询语句,无论合法还是出错的.
2.提交了多个合法的sql查询语句,
但是,如果提交多个查询,且其中一个查询出错(比如statement的语法有错),那么随后的最后一个合法查询就会异常.比如说我先后提交了两个查询:
query('UPDATE WHERE (id = 1)') --语法错误
query('SELET * FROM table1') --正常
query('SELET * FROM table1') --这个合法语句将异常,返回nil,nil,nil,nil
这是我定义的query函数:
local mysql = require "resty.mysql"
local connect_table = {
host = '127.0.0.1', port = 3306,
user = 'root', password = '',
database = 'test',
}
local function query(sql_statements)
local db = ngx.ctx._db
local res, err, errno, sqlstate;
-- 如果第一次建立连接或上一个链接出错, 则新建
if not db or db._query_err~=nil then
db, err = mysql:new()
if not db then
return db, err
end
db:set_timeout(10000)
res, err, errno, sqlstate = db:connect(connect_table)
if not res then
return res, err
end
-- 将db绑定到ctx中, 方便共用
ngx.ctx._db = db
else
--这里表示连接复用(上一次连接是正常的)
end
-- 开始使用连接进行查询
res, err, errno, sqlstate = db:query(sql_statements)
-- 将err值绑定到db上, 以供下次提交查询语句时判断是否需要新建连接
db._query_err = err
return res, err, errno, sqlstate
end
return query
实际在ngx_lua_model中使用时
没注意到有escape过滤用户输入函数
agrs_id = ngx.req.get_post_args()[''id] --
sql = "select * from tb1 where id=" .. agrs_id';'
db:query(sql)
此处应该有id参数是否sql注入可能?
I send restful req to lua-resty-mysql. In the key-value parameter, value is urlencoded.
response is error.
[HEADER_OUT]POST /rest HTTP/1.1
Host: xxx.xxxxxx.com
Accept: /
Content-Length: 497
Content-Type: application/x-www-form-urlencoded
............
[HEADER_IN]HTTP/1.1 500 Internal Server Error
[HEADER_IN]Server: ngx_openresty
[HEADER_IN]Date: Wed, 20 Nov 2013 04:57:57 GMT
[HEADER_IN]Content-Type: text/html
[HEADER_IN]Content-Length: 186
[HEADER_IN]Connection: close
[DATA_IN]
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.