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