From 102a9130f6c884411b89a33c668928e5f85422c1 Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma@tarantool.org>
Date: Tue, 15 May 2018 21:25:28 +0300
Subject: [PATCH] sql: COLLATE after LIMIT throws an error
Originally, SQLite3 execute queries with COLLATE after LIMIT like
"SELECT * FROM test LIMIT N COLLATE not_exist"
and queries without COLLATE like
"SELECT * FROM test LIMIT N"
the same way.
From this patch on queries with COLLATE after LIMIT
throws a syntax error.
Closes: #3010
---
src/box/sql/parse.y | 20 +++++++++----
test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua | 29 ++++++++++++++++++-
test/sql/errinj.result | 35 +++++++++++++++++++++++
test/sql/errinj.test.lua | 17 +++++++++++
4 files changed, 95 insertions(+), 6 deletions(-)
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 872647d..030d58a 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -711,11 +711,21 @@ having_opt(A) ::= HAVING expr(X). {A = X.pExpr;}
// sqlite3ExprDelete(pParse->db, $$.pOffset);
//}
limit_opt(A) ::= . {A.pLimit = 0; A.pOffset = 0;}
-limit_opt(A) ::= LIMIT expr(X). {A.pLimit = X.pExpr; A.pOffset = 0;}
-limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y).
- {A.pLimit = X.pExpr; A.pOffset = Y.pExpr;}
-limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y).
- {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;}
+limit_opt(A) ::= LIMIT expr(X). {
+ if(X.pExpr->flags & EP_Collate)
+ sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+ A.pLimit = X.pExpr; A.pOffset = 0;
+}
+limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). {
+ if((X.pExpr->flags & EP_Collate) || (Y.pExpr->flags & EP_Collate))
+ sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+ A.pLimit = X.pExpr; A.pOffset = Y.pExpr;
+}
+limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {
+ if((X.pExpr->flags & EP_Collate) || (Y.pExpr->flags & EP_Collate))
+ sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+ A.pOffset = X.pExpr; A.pLimit = Y.pExpr;
+}
/////////////////////////// The DELETE statement /////////////////////////////
//
diff --git a/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua b/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua
index 74d69aa..6cdc2ab 100755
--- a/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua
+++ b/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(1)
+test:plan(4)
local ok = pcall(test.execsql, test, [[
DROP TABLE IF EXISTS t1;
@@ -9,4 +9,31 @@ local ok = pcall(test.execsql, test, [[
test:ok(not ok, 'rowid syntax must be forbidden')
+-- gh-3010: COLLATE after LIMIT should throw an error (First part)
+
+test:do_catchsql_test(
+ "collate-after-limit-1.0",
+ "SELECT 1 LIMIT 1 COLLATE BINARY;", {
+ -- <collate-after-limit-1.0>
+ 1, "near \"COLLATE\": syntax error"
+ -- <collate-after-limit-1.0>
+});
+
+test:do_catchsql_test(
+ "collate-after-limit-1.1",
+ "SELECT 1 LIMIT 1 OFFSET 2 COLLATE BINARY;", {
+ -- <collate-after-limit-1.1>
+ 1, "near \"COLLATE\": syntax error"
+ -- <collate-after-limit-1.1>
+});
+
+test:do_catchsql_test(
+ "collate-after-limit-1.2",
+ "SELECT 1 LIMIT 1 COLLATE BINARY, 2;", {
+ -- <collate-after-limit-1.2>
+ 1, "near \"COLLATE\": syntax error"
+ -- <collate-after-limit-1.2>
+});
+
+
test:finish_test()
diff --git a/test/sql/errinj.result b/test/sql/errinj.result
index 00a0d6b..e667091 100644
--- a/test/sql/errinj.result
+++ b/test/sql/errinj.result
@@ -115,3 +115,38 @@ box.sql.execute('DROP TABLE test')
box.schema.user.revoke('guest', 'read,write,execute', 'universe')
---
...
+cn:close()
+---
+...
+-- gh-3010: COLLATE after LIMIT should throw an error (Second part)
+box.sql.execute('CREATE TABLE test (id integer primary key)')
+---
+...
+box.schema.user.grant('guest','read,write,execute', 'universe')
+---
+...
+cn = remote.connect(box.cfg.listen)
+---
+...
+cn:ping()
+---
+- true
+...
+cn:execute('insert into test values (1)')
+---
+- rowcount: 1
+...
+-- SQL error: syntax error
+cn:execute('select * from test limit ? collate not_exist', {1})
+---
+- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'
+...
+cn:close()
+---
+...
+box.sql.execute('DROP TABLE test')
+---
+...
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
+---
+...
diff --git a/test/sql/errinj.test.lua b/test/sql/errinj.test.lua
index 63d3063..1268436 100644
--- a/test/sql/errinj.test.lua
+++ b/test/sql/errinj.test.lua
@@ -44,3 +44,20 @@ errinj.set("ERRINJ_IPROTO_TX_DELAY", false)
box.sql.execute('DROP TABLE test')
box.schema.user.revoke('guest', 'read,write,execute', 'universe')
+
+
+cn:close()
+
+-- gh-3010: COLLATE after LIMIT should throw an error (Second part)
+box.sql.execute('CREATE TABLE test (id integer primary key)')
+box.schema.user.grant('guest','read,write,execute', 'universe')
+cn = remote.connect(box.cfg.listen)
+cn:ping()
+
+cn:execute('insert into test values (1)')
+-- SQL error: syntax error
+cn:execute('select * from test limit ? collate not_exist', {1})
+
+cn:close()
+box.sql.execute('DROP TABLE test')
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
--
2.7.4