[tarantool-patches] [PATCH v2 1/1] sql: parameter binding for box.execute()
Vladislav Shpilevoy
v.shpilevoy at tarantool.org
Mon Apr 1 23:02:42 MSK 2019
From: Mergen Imeev <imeevma at gmail.com>
This patch defines parameters binding for SQL statements executed
through box.execute().
Closes #3401
---
Changes in v2:
- All the code is moved to box/lua/execute.h
V1: https://www.freelists.org/post/tarantool-patches/PATCH-v1-11-sql-parameter-binding-for-boxexecute
Branch: https://github.com/tarantool/tarantool/tree/imeevma/gh-3401-add-binding-to-box_execute
Issue: https://github.com/tarantool/tarantool/issues/3401
src/box/lua/execute.c | 179 ++++++++++++++++++++++-
test/sql/bind.result | 296 +++++++++++++++++++++++++++++++++++++++
test/sql/bind.test.lua | 100 +++++++++++++
test/sql/engine.cfg | 4 +
test/sql/iproto.result | 225 -----------------------------
test/sql/iproto.test.lua | 69 ---------
6 files changed, 577 insertions(+), 296 deletions(-)
create mode 100644 test/sql/bind.result
create mode 100644 test/sql/bind.test.lua
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index ab1f7cd62..71d64bc27 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -1,8 +1,10 @@
#include "execute.h"
#include "lua/utils.h"
+#include "lua/msgpack.h"
#include "box/sql/sqlInt.h"
#include "box/port.h"
#include "box/execute.h"
+#include "box/bind.h"
/**
* Serialize a description of the prepared statement.
@@ -74,6 +76,170 @@ port_sql_dump_lua(struct port *port, struct lua_State *L)
}
}
+/**
+ * Decode a single bind column from Lua stack.
+ *
+ * @param L Lua stack.
+ * @param[out] bind Bind to decode to.
+ * @param idx Position of table with bind columns on Lua stack.
+ * @param i Ordinal bind number.
+ *
+ * @retval 0 Success.
+ * @retval -1 Memory or client error.
+ */
+static inline int
+lua_sql_bind_decode(struct lua_State *L, struct sql_bind *bind, int idx, int i)
+{
+ struct luaL_field field;
+ struct region *region = &fiber()->gc;
+ char *buf;
+ lua_rawgeti(L, idx, i + 1);
+ bind->pos = i + 1;
+ if (lua_istable(L, -1)) {
+ /*
+ * Get key and value of the only table element to
+ * lua stack.
+ */
+ lua_pushnil(L);
+ lua_next(L, -2);
+ if (! lua_isstring(L, -2)) {
+ diag_set(ClientError, ER_ILLEGAL_PARAMS, "name of the "\
+ "parameter should be a string.");
+ return -1;
+ }
+ /* Check that the table is one-row sized. */
+ lua_pushvalue(L, -2);
+ if (lua_next(L, -4) != 0) {
+ diag_set(ClientError, ER_ILLEGAL_PARAMS, "SQL bind "\
+ "named parameter should be a table with "\
+ "one key - {name = value}");
+ return -1;
+ }
+ size_t name_len;
+ bind->name = lua_tolstring(L, -2, &name_len);
+ /*
+ * Name should be saved in allocated memory as it
+ * will be poped from Lua stack.
+ */
+ buf = region_alloc(region, name_len + 1);
+ if (buf == NULL) {
+ diag_set(OutOfMemory, name_len + 1, "region_alloc",
+ "buf");
+ return -1;
+ }
+ memcpy(buf, bind->name, name_len + 1);
+ bind->name = buf;
+ bind->name_len = name_len;
+ } else {
+ bind->name = NULL;
+ bind->name_len = 0;
+ }
+ if (luaL_tofield(L, luaL_msgpack_default, -1, &field) < 0)
+ return -1;
+ switch (field.type) {
+ case MP_UINT:
+ if ((uint64_t) field.ival > INT64_MAX) {
+ diag_set(ClientError, ER_SQL_BIND_VALUE,
+ sql_bind_name(bind), "INTEGER");
+ return -1;
+ }
+ FALLTHROUGH;
+ case MP_INT:
+ bind->i64 = field.ival;
+ bind->type = SQL_INTEGER;
+ bind->bytes = sizeof(bind->i64);
+ break;
+ case MP_STR:
+ /*
+ * Data should be saved in allocated memory as it
+ * will be poped from Lua stack.
+ */
+ buf = region_alloc(region, field.sval.len + 1);
+ if (buf == NULL) {
+ diag_set(OutOfMemory, field.sval.len + 1,
+ "region_alloc", "buf");
+ return -1;
+ }
+ memcpy(buf, field.sval.data, field.sval.len + 1);
+ bind->s = buf;
+ bind->type = SQL_TEXT;
+ bind->bytes = field.sval.len;
+ break;
+ case MP_DOUBLE:
+ case MP_FLOAT:
+ bind->d = field.dval;
+ bind->type = SQL_FLOAT;
+ bind->bytes = sizeof(bind->d);
+ break;
+ case MP_NIL:
+ bind->type = SQL_NULL;
+ bind->bytes = 1;
+ break;
+ case MP_BOOL:
+ /* SQLite doesn't support boolean. Use int instead. */
+ bind->i64 = field.bval ? 1 : 0;
+ bind->type = SQL_INTEGER;
+ bind->bytes = sizeof(bind->i64);
+ break;
+ case MP_BIN:
+ bind->s = mp_decode_bin(&field.sval.data, &bind->bytes);
+ bind->type = SQL_BLOB;
+ break;
+ case MP_EXT:
+ diag_set(ClientError, ER_SQL_BIND_TYPE, "USERDATA",
+ sql_bind_name(bind));
+ return -1;
+ case MP_ARRAY:
+ diag_set(ClientError, ER_SQL_BIND_TYPE, "ARRAY",
+ sql_bind_name(bind));
+ return -1;
+ case MP_MAP:
+ diag_set(ClientError, ER_SQL_BIND_TYPE, "MAP",
+ sql_bind_name(bind));
+ return -1;
+ default:
+ unreachable();
+ }
+ lua_pop(L, lua_gettop(L) - idx);
+ return 0;
+}
+
+int
+lua_sql_bind_list_decode(struct lua_State *L, struct sql_bind **out_bind,
+ int idx)
+{
+ assert(out_bind != NULL);
+ uint32_t bind_count = lua_objlen(L, idx);
+ if (bind_count == 0)
+ return 0;
+ if (bind_count > SQL_BIND_PARAMETER_MAX) {
+ diag_set(ClientError, ER_SQL_BIND_PARAMETER_MAX,
+ (int) bind_count);
+ return -1;
+ }
+ struct region *region = &fiber()->gc;
+ uint32_t used = region_used(region);
+ size_t size = sizeof(struct sql_bind) * bind_count;
+ /*
+ * Memory allocated here will be freed in
+ * sql_finalize() or in txn_commit()/txn_rollback() if
+ * there is an active transaction.
+ */
+ struct sql_bind *bind = (struct sql_bind *) region_alloc(region, size);
+ if (bind == NULL) {
+ diag_set(OutOfMemory, size, "region_alloc", "bind");
+ return -1;
+ }
+ for (uint32_t i = 0; i < bind_count; ++i) {
+ if (lua_sql_bind_decode(L, &bind[i], idx, i) != 0) {
+ region_truncate(region, used);
+ return -1;
+ }
+ }
+ *out_bind = bind;
+ return bind_count;
+}
+
static int
lbox_execute(struct lua_State *L)
{
@@ -83,10 +249,19 @@ lbox_execute(struct lua_State *L)
struct port port;
int top = lua_gettop(L);
- if (top != 1 || ! lua_isstring(L, 1))
- return luaL_error(L, "Usage: box.execute(sqlstring)");
+ if ((top != 1 && top != 2) || ! lua_isstring(L, 1))
+ return luaL_error(L, "Usage: box.execute(sqlstring[, params])");
const char *sql = lua_tolstring(L, 1, &length);
+
+ if (top == 2) {
+ if (! lua_istable(L, 2))
+ return luaL_error(L, "Second argument must be a table");
+ bind_count = lua_sql_bind_list_decode(L, &bind, 2);
+ if (bind_count < 0)
+ return luaT_error(L);
+ }
+
if (sql_prepare_and_execute(sql, length, bind, bind_count, &port,
&fiber()->gc) != 0)
return luaT_error(L);
diff --git a/test/sql/bind.result b/test/sql/bind.result
new file mode 100644
index 000000000..acf23a52e
--- /dev/null
+++ b/test/sql/bind.result
@@ -0,0 +1,296 @@
+netbox = require('net.box')
+---
+...
+test_run = require('test_run').new()
+---
+...
+box.execute('CREATE TABLE test (id INT PRIMARY KEY, a FLOAT, b TEXT)')
+---
+- rowcount: 1
+...
+box.space.TEST:replace{1, 2, '3'}
+---
+- [1, 2, '3']
+...
+box.space.TEST:replace{7, 8.5, '9'}
+---
+- [7, 8.5, '9']
+...
+box.space.TEST:replace{10, 11, box.NULL}
+---
+- [10, 11, null]
+...
+remote = test_run:get_cfg('remote') == 'true'
+---
+...
+execute = nil
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+if remote then
+ box.schema.user.grant('guest','read, write, execute', 'universe')
+ box.schema.user.grant('guest', 'create', 'space')
+ cn = netbox.connect(box.cfg.listen)
+ execute = function(...) return cn:execute(...) end
+else
+ execute = box.execute
+end;
+---
+...
+test_run:cmd("setopt delimiter ''");
+---
+- true
+...
+--
+-- gh-3401: box.execute parameter binding.
+--
+parameters = {}
+---
+...
+parameters[1] = {}
+---
+...
+parameters[1][':value'] = 1
+---
+...
+execute('SELECT * FROM test WHERE id = :value', parameters)
+---
+- metadata:
+ - name: ID
+ type: INTEGER
+ - name: A
+ type: NUMERIC
+ - name: B
+ type: TEXT
+ rows:
+ - [1, 2, '3']
+...
+execute('SELECT ?, ?, ?', {1, 2, 3})
+---
+- metadata:
+ - name: '?'
+ type: INTEGER
+ - name: '?'
+ type: INTEGER
+ - name: '?'
+ type: INTEGER
+ rows:
+ - [1, 2, 3]
+...
+parameters = {}
+---
+...
+parameters[1] = 10
+---
+...
+parameters[2] = {}
+---
+...
+parameters[2]['@value2'] = 12
+---
+...
+parameters[3] = {}
+---
+...
+parameters[3][':value1'] = 11
+---
+...
+execute('SELECT ?, :value1, @value2', parameters)
+---
+- metadata:
+ - name: '?'
+ type: INTEGER
+ - name: :value1
+ type: INTEGER
+ - name: '@value2'
+ type: INTEGER
+ rows:
+ - [10, 11, 12]
+...
+parameters = {}
+---
+...
+parameters[1] = {}
+---
+...
+parameters[1][':value3'] = 1
+---
+...
+parameters[2] = 2
+---
+...
+parameters[3] = {}
+---
+...
+parameters[3][':value1'] = 3
+---
+...
+parameters[4] = 4
+---
+...
+parameters[5] = 5
+---
+...
+parameters[6] = {}
+---
+...
+parameters[6]['@value2'] = 6
+---
+...
+execute('SELECT :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters)
+---
+- metadata:
+ - name: :value3
+ type: INTEGER
+ - name: '?'
+ type: INTEGER
+ - name: :value1
+ type: INTEGER
+ - name: '?'
+ type: INTEGER
+ - name: '?'
+ type: INTEGER
+ - name: '@value2'
+ type: INTEGER
+ - name: '?'
+ type: BOOLEAN
+ - name: :value3
+ type: INTEGER
+ rows:
+ - [1, 2, 3, 4, 5, 6, null, 1]
+...
+-- Try not-integer types.
+msgpack = require('msgpack')
+---
+...
+execute('SELECT ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false})
+---
+- metadata:
+ - name: '?'
+ type: TEXT
+ - name: '?'
+ type: NUMERIC
+ - name: '?'
+ type: BOOLEAN
+ - name: '?'
+ type: INTEGER
+ - name: '?'
+ type: INTEGER
+ rows:
+ - ['abc', -123.456, null, 1, 0]
+...
+-- Try to replace '?' in meta with something meaningful.
+execute('SELECT ? AS kek, ? AS kek2', {1, 2})
+---
+- metadata:
+ - name: KEK
+ type: INTEGER
+ - name: KEK2
+ type: INTEGER
+ rows:
+ - [1, 2]
+...
+-- Try to bind not existing name.
+parameters = {}
+---
+...
+parameters[1] = {}
+---
+...
+parameters[1]['name'] = 300
+---
+...
+execute('SELECT ? AS kek', parameters)
+---
+- error: Parameter 'name' was not found in the statement
+...
+-- Try too many parameters in a statement.
+sql = 'SELECT '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?'
+---
+...
+execute(sql)
+---
+- error: 'SQL bind parameter limit reached: 65000'
+...
+-- Try too many parameter values.
+sql = 'SELECT ?'
+---
+...
+parameters = {}
+---
+...
+for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end
+---
+...
+execute(sql, parameters)
+---
+- error: 'SQL bind parameter limit reached: 65001'
+...
+--
+-- Errors during parameters binding.
+--
+-- Try value > INT64_MAX. sql can't bind it, since it has no
+-- suitable method in its bind API.
+execute('SELECT ? AS big_uint', {0xefffffffffffffff})
+---
+- error: Bind value for parameter 1 is out of range for type INTEGER
+...
+-- Bind incorrect parameters.
+ok, err = pcall(execute, 'SELECT ?', { {1, 2, 3} })
+---
+...
+ok
+---
+- false
+...
+parameters = {}
+---
+...
+parameters[1] = {}
+---
+...
+parameters[1][100] = 200
+---
+...
+ok, err = pcall(execute, 'SELECT ?', parameters)
+---
+...
+ok
+---
+- false
+...
+parameters = {}
+---
+...
+parameters[1] = {}
+---
+...
+parameters[1][':value'] = {kek = 300}
+---
+...
+execute('SELECT :value', parameters)
+---
+- error: Bind value type MAP for parameter ':value' is not supported
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+if remote then
+ cn:close()
+ box.schema.user.revoke('guest', 'read, write, execute', 'universe')
+ box.schema.user.revoke('guest', 'create', 'space')
+end;
+---
+...
+test_run:cmd("setopt delimiter ''");
+---
+- true
+...
+box.execute('DROP TABLE test')
+---
+- rowcount: 1
+...
diff --git a/test/sql/bind.test.lua b/test/sql/bind.test.lua
new file mode 100644
index 000000000..229207d3a
--- /dev/null
+++ b/test/sql/bind.test.lua
@@ -0,0 +1,100 @@
+netbox = require('net.box')
+test_run = require('test_run').new()
+
+box.execute('CREATE TABLE test (id INT PRIMARY KEY, a FLOAT, b TEXT)')
+box.space.TEST:replace{1, 2, '3'}
+box.space.TEST:replace{7, 8.5, '9'}
+box.space.TEST:replace{10, 11, box.NULL}
+
+remote = test_run:get_cfg('remote') == 'true'
+execute = nil
+test_run:cmd("setopt delimiter ';'")
+if remote then
+ box.schema.user.grant('guest','read, write, execute', 'universe')
+ box.schema.user.grant('guest', 'create', 'space')
+ cn = netbox.connect(box.cfg.listen)
+ execute = function(...) return cn:execute(...) end
+else
+ execute = box.execute
+end;
+test_run:cmd("setopt delimiter ''");
+--
+-- gh-3401: box.execute parameter binding.
+--
+parameters = {}
+parameters[1] = {}
+parameters[1][':value'] = 1
+execute('SELECT * FROM test WHERE id = :value', parameters)
+execute('SELECT ?, ?, ?', {1, 2, 3})
+parameters = {}
+parameters[1] = 10
+parameters[2] = {}
+parameters[2]['@value2'] = 12
+parameters[3] = {}
+parameters[3][':value1'] = 11
+execute('SELECT ?, :value1, @value2', parameters)
+
+parameters = {}
+parameters[1] = {}
+parameters[1][':value3'] = 1
+parameters[2] = 2
+parameters[3] = {}
+parameters[3][':value1'] = 3
+parameters[4] = 4
+parameters[5] = 5
+parameters[6] = {}
+parameters[6]['@value2'] = 6
+execute('SELECT :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters)
+
+-- Try not-integer types.
+msgpack = require('msgpack')
+execute('SELECT ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false})
+
+-- Try to replace '?' in meta with something meaningful.
+execute('SELECT ? AS kek, ? AS kek2', {1, 2})
+
+-- Try to bind not existing name.
+parameters = {}
+parameters[1] = {}
+parameters[1]['name'] = 300
+execute('SELECT ? AS kek', parameters)
+
+-- Try too many parameters in a statement.
+sql = 'SELECT '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?'
+execute(sql)
+
+-- Try too many parameter values.
+sql = 'SELECT ?'
+parameters = {}
+for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end
+execute(sql, parameters)
+
+--
+-- Errors during parameters binding.
+--
+-- Try value > INT64_MAX. sql can't bind it, since it has no
+-- suitable method in its bind API.
+execute('SELECT ? AS big_uint', {0xefffffffffffffff})
+-- Bind incorrect parameters.
+ok, err = pcall(execute, 'SELECT ?', { {1, 2, 3} })
+ok
+parameters = {}
+parameters[1] = {}
+parameters[1][100] = 200
+ok, err = pcall(execute, 'SELECT ?', parameters)
+ok
+
+parameters = {}
+parameters[1] = {}
+parameters[1][':value'] = {kek = 300}
+execute('SELECT :value', parameters)
+
+test_run:cmd("setopt delimiter ';'")
+if remote then
+ cn:close()
+ box.schema.user.revoke('guest', 'read, write, execute', 'universe')
+ box.schema.user.revoke('guest', 'create', 'space')
+end;
+test_run:cmd("setopt delimiter ''");
+
+box.execute('DROP TABLE test')
diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg
index 5ac445108..284c42082 100644
--- a/test/sql/engine.cfg
+++ b/test/sql/engine.cfg
@@ -5,6 +5,10 @@
"sql-debug.test.lua": {
"memtx": {"engine": "memtx"}
},
+ "bind.test.lua": {
+ "remote": {"remote": "true"},
+ "local": {"remote": "false"}
+ },
"*": {
"memtx": {"engine": "memtx"},
"vinyl": {"engine": "vinyl"}
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 18e305276..112225bb6 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -198,231 +198,6 @@ cn:execute('select * from test limit 1 offset ?', {'Hello'})
---
- error: Only positive integers are allowed in the OFFSET clause
...
---
--- Parameters binding.
---
-parameters = {}
----
-...
-parameters[1] = {}
----
-...
-parameters[1][':value'] = 1
----
-...
-cn:execute('select * from test where id = :value', parameters)
----
-- metadata:
- - name: ID
- type: INTEGER
- - name: A
- type: NUMERIC
- - name: B
- type: TEXT
- rows:
- - [1, 2, '3']
-...
-cn:execute('select ?, ?, ?', {1, 2, 3})
----
-- metadata:
- - name: '?'
- type: INTEGER
- - name: '?'
- type: INTEGER
- - name: '?'
- type: INTEGER
- rows:
- - [1, 2, 3]
-...
-parameters = {}
----
-...
-parameters[1] = 10
----
-...
-parameters[2] = {}
----
-...
-parameters[2]['@value2'] = 12
----
-...
-parameters[3] = {}
----
-...
-parameters[3][':value1'] = 11
----
-...
-cn:execute('select ?, :value1, @value2', parameters)
----
-- metadata:
- - name: '?'
- type: INTEGER
- - name: :value1
- type: INTEGER
- - name: '@value2'
- type: INTEGER
- rows:
- - [10, 11, 12]
-...
-parameters = {}
----
-...
-parameters[1] = {}
----
-...
-parameters[1][':value3'] = 1
----
-...
-parameters[2] = 2
----
-...
-parameters[3] = {}
----
-...
-parameters[3][':value1'] = 3
----
-...
-parameters[4] = 4
----
-...
-parameters[5] = 5
----
-...
-parameters[6] = {}
----
-...
-parameters[6]['@value2'] = 6
----
-...
-cn:execute('select :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters)
----
-- metadata:
- - name: :value3
- type: INTEGER
- - name: '?'
- type: INTEGER
- - name: :value1
- type: INTEGER
- - name: '?'
- type: INTEGER
- - name: '?'
- type: INTEGER
- - name: '@value2'
- type: INTEGER
- - name: '?'
- type: BOOLEAN
- - name: :value3
- type: INTEGER
- rows:
- - [1, 2, 3, 4, 5, 6, null, 1]
-...
--- Try not-integer types.
-msgpack = require('msgpack')
----
-...
-cn:execute('select ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false})
----
-- metadata:
- - name: '?'
- type: TEXT
- - name: '?'
- type: NUMERIC
- - name: '?'
- type: BOOLEAN
- - name: '?'
- type: INTEGER
- - name: '?'
- type: INTEGER
- rows:
- - ['abc', -123.456, null, 1, 0]
-...
--- Try to replace '?' in meta with something meaningful.
-cn:execute('select ? as kek, ? as kek2', {1, 2})
----
-- metadata:
- - name: KEK
- type: INTEGER
- - name: KEK2
- type: INTEGER
- rows:
- - [1, 2]
-...
--- Try to bind not existing name.
-parameters = {}
----
-...
-parameters[1] = {}
----
-...
-parameters[1]['name'] = 300
----
-...
-cn:execute('select ? as kek', parameters)
----
-- error: Parameter 'name' was not found in the statement
-...
--- Try too many parameters in a statement.
-sql = 'select '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?'
----
-...
-cn:execute(sql)
----
-- error: 'SQL bind parameter limit reached: 65000'
-...
--- Try too many parameter values.
-sql = 'select ?'
----
-...
-parameters = {}
----
-...
-for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end
----
-...
-cn:execute(sql, parameters)
----
-- error: 'SQL bind parameter limit reached: 65001'
-...
---
--- Errors during parameters binding.
---
--- Try value > INT64_MAX. sql can't bind it, since it has no
--- suitable method in its bind API.
-cn:execute('select ? as big_uint', {0xefffffffffffffff})
----
-- error: Bind value for parameter 1 is out of range for type INTEGER
-...
--- Bind incorrect parameters.
-cn:execute('select ?', { {1, 2, 3} })
----
-- error: Bind value type ARRAY for parameter 1 is not supported
-...
-parameters = {}
----
-...
-parameters[1] = {}
----
-...
-parameters[1][100] = 200
----
-...
-cn:execute('select ?', parameters)
----
-- error: Invalid MsgPack - SQL bind parameter
-...
-parameters = {}
----
-...
-parameters[1] = {}
----
-...
-parameters[1][':value'] = {kek = 300}
----
-...
-cn:execute('select :value', parameters)
----
-- error: Bind value type MAP for parameter ':value' is not supported
-...
-- gh-2608 SQL iproto DDL
cn:execute('create table test2(id int primary key, a int, b int, c int)')
---
diff --git a/test/sql/iproto.test.lua b/test/sql/iproto.test.lua
index 6a6c24ad9..fc27ca620 100644
--- a/test/sql/iproto.test.lua
+++ b/test/sql/iproto.test.lua
@@ -58,75 +58,6 @@ cn:execute('select * from test limit 1 offset ?', {-2})
cn:execute('select * from test limit 1 offset ?', {2.7})
cn:execute('select * from test limit 1 offset ?', {'Hello'})
---
--- Parameters binding.
---
-parameters = {}
-parameters[1] = {}
-parameters[1][':value'] = 1
-cn:execute('select * from test where id = :value', parameters)
-cn:execute('select ?, ?, ?', {1, 2, 3})
-parameters = {}
-parameters[1] = 10
-parameters[2] = {}
-parameters[2]['@value2'] = 12
-parameters[3] = {}
-parameters[3][':value1'] = 11
-cn:execute('select ?, :value1, @value2', parameters)
-
-parameters = {}
-parameters[1] = {}
-parameters[1][':value3'] = 1
-parameters[2] = 2
-parameters[3] = {}
-parameters[3][':value1'] = 3
-parameters[4] = 4
-parameters[5] = 5
-parameters[6] = {}
-parameters[6]['@value2'] = 6
-cn:execute('select :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters)
-
--- Try not-integer types.
-msgpack = require('msgpack')
-cn:execute('select ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false})
-
--- Try to replace '?' in meta with something meaningful.
-cn:execute('select ? as kek, ? as kek2', {1, 2})
-
--- Try to bind not existing name.
-parameters = {}
-parameters[1] = {}
-parameters[1]['name'] = 300
-cn:execute('select ? as kek', parameters)
-
--- Try too many parameters in a statement.
-sql = 'select '..string.rep('?, ', box.schema.SQL_BIND_PARAMETER_MAX)..'?'
-cn:execute(sql)
-
--- Try too many parameter values.
-sql = 'select ?'
-parameters = {}
-for i = 1, box.schema.SQL_BIND_PARAMETER_MAX + 1 do parameters[i] = i end
-cn:execute(sql, parameters)
-
---
--- Errors during parameters binding.
---
--- Try value > INT64_MAX. sql can't bind it, since it has no
--- suitable method in its bind API.
-cn:execute('select ? as big_uint', {0xefffffffffffffff})
--- Bind incorrect parameters.
-cn:execute('select ?', { {1, 2, 3} })
-parameters = {}
-parameters[1] = {}
-parameters[1][100] = 200
-cn:execute('select ?', parameters)
-
-parameters = {}
-parameters[1] = {}
-parameters[1][':value'] = {kek = 300}
-cn:execute('select :value', parameters)
-
-- gh-2608 SQL iproto DDL
cn:execute('create table test2(id int primary key, a int, b int, c int)')
box.space.TEST2.name
--
2.17.2 (Apple Git-113)
More information about the Tarantool-patches
mailing list