Subject: bug#69320: 30.0.50; Support keyword-based substitutions in sqlite

From: "J.P." <[email-masked]>
Date: Sat, 24 Feb 2024 07:04:32 -0800

"J.P." <[email-masked]> writes:

>> @@ -333,6 +338,7 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
>>  {
>>    sqlite3_reset (stmt);
>>    int len;
>> +  int kw_dex = 0;
>
> I think one way to support interspersed param types would be to maintain
> two separate indexes, e.g,
>
>      int pos_dex = 0;
>
>>    if (VECTORP (values))
>>      len = ASIZE (values);
>>    else
>> @@ -341,6 +347,7 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
>>    for (int i = 0; i < len; ++i)
>>      {
>>        int ret = SQLITE_MISMATCH;
>> +      int j = (kw_dex ? kw_dex : i + 1);
>
> which would mean changing this to something like
>
>          int j = (kw_dex ? kw_dex : ++pos_dex);

Actually, nah. This is bogus in many cases. It seems my mental model of
how these specifiers map to value-binding indices was based mostly on
magical thinking. I didn't realize, for one, that anonymous (positional)
specifiers and named ones share the same index space. AFAICT, this means
iterating over supplied parameters twice is unavoidable if we want to
support this feature in full. I've attached an updated version that
demos this approach even though I'm not super keen on it. It's somewhat
wasteful and definitely more complex. If anyone out there knows of a
smarter way, please do indulge me.

If a less ugly solution doesn't come about, I suppose we could impose an
artificial limitation saying that an argument list must either be
entirely one style or the other (positional or named), and never the two
shall meet. On the one hand, there seems to be some historical precedent
treating this as the recommended usage [1]. On the other hand, opting
for such a "nerfed" implementation (like my initial patch) may feel like
a cop out. If so, then it's probably best to stick with the status quo
and not support named parameters at all.

[1] "While all these forms are allowed, it is expected that different
    users will use different styles at different times."

    https://www.mail-archive.com/[email-masked]/msg05313.html

>From 8b00d4a7b4af152aea0d308d1c79f0ff97e21dbd Mon Sep 17 00:00:00 2001
From: "F. Jason Park" <[email-masked]>
Date: Sat, 24 Feb 2024 06:58:25 -0800
Subject: [PATCH 0/1] *** NOT A PATCH ***

*** BLURB HERE ***

F. Jason Park (1):
  Recognize keyword template specifiers in sqlite

 doc/lispref/text.texi    |   9 +-
 src/sqlite.c             | 186 ++++++++++++++++++++++++++-------------
 test/src/sqlite-tests.el |  74 ++++++++++++++++
 3 files changed, 205 insertions(+), 64 deletions(-)

Interdiff:
diff --git a/src/sqlite.c b/src/sqlite.c
index 123182d241b..f446f35fe2a 100644
--- a/src/sqlite.c
+++ b/src/sqlite.c
@@ -331,93 +331,142 @@ DEFUN ("sqlite-close", Fsqlite_close, Ssqlite_close, 1, 1, 0,
   return Qt;
 }
 
+/* Return SQLITE_OK or an SQLite error code or, for a user error, -1. */
+static int
+bind_one_value (sqlite3_stmt *stmt, int dex, Lisp_Object value)
+{
+  Lisp_Object type = Ftype_of (value);
+
+  if (EQ (type, Qstring))
+    {
+      Lisp_Object encoded;
+      bool blob = false;
+
+      if (SBYTES (value) == 0)
+	encoded = value;
+      else
+	{
+	  Lisp_Object coding_system =
+	    Fget_text_property (make_fixnum (0), Qcoding_system, value);
+	  if (NILP (coding_system))
+	    /* Default to utf-8.  */
+	    encoded = encode_string (value);
+	  else if (EQ (coding_system, Qbinary))
+	    blob = true;
+	  else
+	    encoded = Fencode_coding_string (value, coding_system, Qnil, Qnil);
+	}
+
+      if (blob)
+	{
+	  if (SBYTES (value) != SCHARS (value))
+	    xsignal1 (Qsqlite_error,
+		      build_string ("BLOB values must be unibyte"));
+	  return sqlite3_bind_blob (stmt, dex, SSDATA (value),
+				    SBYTES (value), NULL);
+	}
+      else
+	return sqlite3_bind_text (stmt, dex, SSDATA (encoded),
+				  SBYTES (encoded), NULL);
+    }
+  else if (EQ (type, Qinteger))
+    {
+      if (BIGNUMP (value))
+	return sqlite3_bind_int64 (stmt, dex, bignum_to_intmax (value));
+      else
+	return sqlite3_bind_int64 (stmt, dex, XFIXNUM (value));
+    }
+  else if (EQ (type, Qfloat))
+    return sqlite3_bind_double (stmt, dex, XFLOAT_DATA (value));
+  else if (NILP (value))
+    return sqlite3_bind_null (stmt, dex);
+  else if (EQ (value, Qt))
+    return sqlite3_bind_int (stmt, dex, 1);
+  else if (EQ (value, Qfalse))
+    return sqlite3_bind_int (stmt, dex, 0);
+  else
+    return -1;
+}
+
+static Lisp_Object
+get_next_bind_candidate (int i, Lisp_Object *values)
+{
+  Lisp_Object value;
+  if (VECTORP (*values))
+    value = AREF (*values, i);
+  else
+    {
+      value = XCAR (*values);
+      *values = XCDR (*values);
+    }
+  return value;
+}
+
+/* Apparently, 999 was the max number of allowed params back in 2004
+   https://www.mail-archive.com/[email-masked]/msg05313.html */
+#define MAX_SQLITE_PARAMS 999
+
 /* Bind values in a statement like
    "insert into foo values (?, ?, ?)".  */
 static const char *
 bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
 {
   sqlite3_reset (stmt);
+  Lisp_Object orig_values = values;
+  int ret = SQLITE_MISMATCH;
   int len;
-  int kw_dex = 0;
   if (VECTORP (values))
     len = ASIZE (values);
   else
     len = list_length (values);
 
+  bool indexes[MAX_SQLITE_PARAMS] = {0};
+  memset (indexes, 0, sizeof (indexes));
+
+  /* Deal with all named parameters first. */
   for (int i = 0; i < len; ++i)
     {
-      int ret = SQLITE_MISMATCH;
-      int j = (kw_dex ? kw_dex : i + 1);
-      Lisp_Object value;
-      if (VECTORP (values))
-	value = AREF (values, i);
-      else
-	{
-	  value = XCAR (values);
-	  values = XCDR (values);
-	}
-      Lisp_Object type = Ftype_of (value);
-
-      if (EQ (type, Qstring))
-	{
-	  Lisp_Object encoded;
-	  bool blob = false;
+      Lisp_Object value = get_next_bind_candidate (i, &values);
+      if (NILP (Fkeywordp (value)))
+	continue;
+      char *param_name = SSDATA (encode_string (SYMBOL_NAME (value)));
+      int dex = sqlite3_bind_parameter_index (stmt, param_name);
+      if (dex == 0)
+	return "invalid named parameter";
+      indexes[dex] = true;
+      value = get_next_bind_candidate (++i, &values);
+      ret = bind_one_value (stmt, dex, value);
+      if (ret)
+	goto failed;
+    }
 
-	  if (SBYTES (value) == 0)
-	    encoded = value;
-	  else
-	    {
-	      Lisp_Object coding_system =
-		Fget_text_property (make_fixnum (0), Qcoding_system, value);
-	      if (NILP (coding_system))
-		/* Default to utf-8.  */
-		encoded = encode_string (value);
-	      else if (EQ (coding_system, Qbinary))
-		blob = true;
-	      else
-		encoded = Fencode_coding_string (value, coding_system,
-						 Qnil, Qnil);
-	    }
-
-	  if (blob)
-	    {
-	      if (SBYTES (value) != SCHARS (value))
-		xsignal1 (Qsqlite_error, build_string ("BLOB values must be unibyte"));
-	      ret = sqlite3_bind_blob (stmt, j,
-				       SSDATA (value), SBYTES (value),
-				       NULL);
-	    }
-	    else
-	      ret = sqlite3_bind_text (stmt, j,
-				       SSDATA (encoded), SBYTES (encoded),
-				       NULL);
-	}
-      else if (EQ (type, Qinteger))
+  /* Use unoccupied indexes for positional params. */
+  int pos_dex = 0;
+  values = orig_values;
+  for (int i = 0; i < len; ++i)
+    {
+      Lisp_Object value = get_next_bind_candidate (i, &values);
+      /* Skip keywords and their associated values. */
+      if (!NILP (Fkeywordp (value)))
 	{
-	  if (BIGNUMP (value))
-	    ret = sqlite3_bind_int64 (stmt, j, bignum_to_intmax (value));
-	  else
-	    ret = sqlite3_bind_int64 (stmt, j, XFIXNUM (value));
+	  if (!(VECTORP (values)))
+	    values = XCDR (values);
+	  i++;
+	  continue;
 	}
-      else if (EQ (type, Qfloat))
-	ret = sqlite3_bind_double (stmt, j, XFLOAT_DATA (value));
-      else if (NILP (value))
-	ret = sqlite3_bind_null (stmt, j);
-      else if (EQ (value, Qt))
-	ret = sqlite3_bind_int (stmt, j, 1);
-      else if (EQ (value, Qfalse))
-	ret = sqlite3_bind_int (stmt, j, 0);
-      else if (!NILP (Fkeywordp (value))
-	       && (kw_dex = sqlite3_bind_parameter_index
-		   (stmt, SSDATA (encode_string (SYMBOL_NAME (value))))))
-	continue;
-      else
-	return "invalid argument";
-      kw_dex = 0;
-      if (ret != SQLITE_OK)
-	return sqlite3_errmsg (db);
+      ret = -1;
+      while ((indexes[++pos_dex]) && pos_dex < MAX_SQLITE_PARAMS);
+      if (pos_dex == MAX_SQLITE_PARAMS - 1)
+	goto failed;
+      ret = bind_one_value (stmt, pos_dex, value);
+      if (ret)
+	break;
     }
-
+ failed:
+  if (ret == -1)
+    return "invalid argument";
+  if (ret != SQLITE_OK)
+    return sqlite3_errmsg (db);
   return NULL;
 }
 
diff --git a/test/src/sqlite-tests.el b/test/src/sqlite-tests.el
index 6c609962196..e7b03bdb5bf 100644
--- a/test/src/sqlite-tests.el
+++ b/test/src/sqlite-tests.el
@@ -173,6 +173,49 @@ sqlite-keyword-params
       (sqlite-select db "SELECT * FROM test4b WHERE v = :a AND w = :a" '(:a 1))
       '(("foo" 1 1 0 3.14159 nil))))))
 
+(ert-deftest sqlite-mixed-params ()
+  (skip-unless (sqlite-available-p))
+  (let ((db (sqlite-open)))
+    (sqlite-execute
+     db "CREATE TABLE IF NOT EXISTS test4c (col1 TEXT, col2 NUMBER)")
+    (sqlite-execute db "INSERT INTO test4c VALUES (:a, ?)" '(:a "foo" 1))
+    (sqlite-execute db "INSERT INTO test4c VALUES (:s, ?)" '(1 :s "spam"))
+    (sqlite-execute db "INSERT INTO test4c VALUES (?, :b)" '("bar" :b 1))
+    (sqlite-execute db "INSERT INTO test4c VALUES (?, :z)" '(:z 1 "baz"))
+    (should (equal (sqlite-select db "SELECT * FROM test4c WHERE col2 = 1")
+                   '(("foo" 1) ("spam" 1) ("bar" 1) ("baz" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+                    '(:a "foo" 1))
+                   '(("foo" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+                    '(1 :a "foo"))
+                   '(("foo" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+                    '("bar" :b 1))
+                   '(("bar" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+                    '("bar" :b 1))
+                   '(("bar" 1))))
+
+    ;; Template comingled specifiers reused.
+    (sqlite-execute
+     db (concat "CREATE TABLE IF NOT EXISTS test4d ("
+                " u, v, w, x, y, z, FOREIGN KEY(v) REFERENCES test4c(rowid)"
+                ")"))
+    (sqlite-execute db (concat "INSERT INTO test4d(u, v, w, x, y, z)"
+                               " SELECT :a, t.rowid, :b, ?, ?, :e"
+                               " FROM test4c as t "
+                               " WHERE t.col1 = :a AND t.col2 = :b")
+                    '(:a "foo" :b t false 3.14159 :e nil)) ; e is NULL
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4d WHERE v = ? AND w = ?1" '(1))
+      '(("foo" 1 1 0 3.14159 nil))))))
+
 (ert-deftest sqlite-binary ()
   (skip-unless (sqlite-available-p))
   (let (db)
-- 
2.43.0

>From 8b00d4a7b4af152aea0d308d1c79f0ff97e21dbd Mon Sep 17 00:00:00 2001
From: "F. Jason Park" <[email-masked]>
Date: Thu, 22 Feb 2024 19:28:24 -0800
Subject: [PATCH 1/1] Recognize keyword template specifiers in sqlite

* doc/lispref/text.texi (Database): Mention keyword-based
substitutions for SQLite statements and queries.
* src/sqlite.c: Add Windows boilerplate to support the dynamic loading
of library function `sqlite3_bind_parameter_index', which first
appeared in SQLite 3.0.7, released 2004.
(bind_one_value): New function containing most of the value-binding
logic from what was `bind_values'.
(get_next_bind_candidate): New helper function.
(bind_values): Refactor to handle named parameters resembling Emacs
keywords.  Factor out the loop body with the various `sqlite3_bind_*'
functions and move them to `bind_one_value'.
* test/src/sqlite-tests.el (sqlite-keyword-params,
sqlite-mixed-params): New test.
---
 doc/lispref/text.texi    |   9 +-
 src/sqlite.c             | 186 ++++++++++++++++++++++++++-------------
 test/src/sqlite-tests.el |  74 ++++++++++++++++
 3 files changed, 205 insertions(+), 64 deletions(-)

diff --git a/doc/lispref/text.texi b/doc/lispref/text.texi
index 3d14a5ad8be..c3b2226e082 100644
--- a/doc/lispref/text.texi
+++ b/doc/lispref/text.texi
@@ -5376,7 +5376,12 @@ Database
 
 This has exactly the same effect as the previous example, but is more
 efficient and safer (because it doesn't involve any string parsing or
-interpolation).
+interpolation).  In addition to positional substitutions, you can also
+use keyword-based replacements, for example:
+
+@lisp
+(sqlite-execute db "insert into foo values (:a, :b)" '(:a "bar" :b 2))
+@end lisp
 
 @code{sqlite-execute} usually returns the number of affected rows.
 For instance, an @samp{insert} statement will typically return
@@ -5418,6 +5423,8 @@ Database
 @lisp
 (sqlite-select db "select * from foo where key = ?" [2])
   @result{} (("bar" 2))
+(sqlite-select db "select * from foo where key = :a" [:a 2])
+  @result{} (("bar" 2))
 @end lisp
 
 This is usually more efficient and safer than the method used by the
diff --git a/src/sqlite.c b/src/sqlite.c
index 7a018b28aa4..f446f35fe2a 100644
--- a/src/sqlite.c
+++ b/src/sqlite.c
@@ -63,6 +63,8 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_bind_int64,
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_double, (sqlite3_stmt*, int, double));
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_null, (sqlite3_stmt*, int));
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_int, (sqlite3_stmt*, int, int));
+DEF_DLL_FN (SQLITE_API int, sqlite3_bind_parameter_index,
+	    (sqlite3_stmt*, const char*));
 DEF_DLL_FN (SQLITE_API int, sqlite3_extended_errcode, (sqlite3*));
 DEF_DLL_FN (SQLITE_API const char*, sqlite3_errmsg, (sqlite3*));
 #if SQLITE_VERSION_NUMBER >= 3007015
@@ -108,6 +110,7 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_db_config, (sqlite3*, int, ...));
 # undef sqlite3_bind_double
 # undef sqlite3_bind_null
 # undef sqlite3_bind_int
+# undef sqlite3_bind_parameter_index
 # undef sqlite3_extended_errcode
 # undef sqlite3_errmsg
 # if SQLITE_VERSION_NUMBER >= 3007015
@@ -137,6 +140,7 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_db_config, (sqlite3*, int, ...));
 # define sqlite3_bind_double fn_sqlite3_bind_double
 # define sqlite3_bind_null fn_sqlite3_bind_null
 # define sqlite3_bind_int fn_sqlite3_bind_int
+# define sqlite3_bind_parameter_index fn_sqlite3_bind_parameter_index
 # define sqlite3_extended_errcode fn_sqlite3_extended_errcode
 # define sqlite3_errmsg fn_sqlite3_errmsg
 # if SQLITE_VERSION_NUMBER >= 3007015
@@ -169,6 +173,7 @@ load_dll_functions (HMODULE library)
   LOAD_DLL_FN (library, sqlite3_bind_double);
   LOAD_DLL_FN (library, sqlite3_bind_null);
   LOAD_DLL_FN (library, sqlite3_bind_int);
+  LOAD_DLL_FN (library, sqlite3_bind_parameter_index);
   LOAD_DLL_FN (library, sqlite3_extended_errcode);
   LOAD_DLL_FN (library, sqlite3_errmsg);
 #if SQLITE_VERSION_NUMBER >= 3007015
@@ -326,87 +331,142 @@ DEFUN ("sqlite-close", Fsqlite_close, Ssqlite_close, 1, 1, 0,
   return Qt;
 }
 
+/* Return SQLITE_OK or an SQLite error code or, for a user error, -1. */
+static int
+bind_one_value (sqlite3_stmt *stmt, int dex, Lisp_Object value)
+{
+  Lisp_Object type = Ftype_of (value);
+
+  if (EQ (type, Qstring))
+    {
+      Lisp_Object encoded;
+      bool blob = false;
+
+      if (SBYTES (value) == 0)
+	encoded = value;
+      else
+	{
+	  Lisp_Object coding_system =
+	    Fget_text_property (make_fixnum (0), Qcoding_system, value);
+	  if (NILP (coding_system))
+	    /* Default to utf-8.  */
+	    encoded = encode_string (value);
+	  else if (EQ (coding_system, Qbinary))
+	    blob = true;
+	  else
+	    encoded = Fencode_coding_string (value, coding_system, Qnil, Qnil);
+	}
+
+      if (blob)
+	{
+	  if (SBYTES (value) != SCHARS (value))
+	    xsignal1 (Qsqlite_error,
+		      build_string ("BLOB values must be unibyte"));
+	  return sqlite3_bind_blob (stmt, dex, SSDATA (value),
+				    SBYTES (value), NULL);
+	}
+      else
+	return sqlite3_bind_text (stmt, dex, SSDATA (encoded),
+				  SBYTES (encoded), NULL);
+    }
+  else if (EQ (type, Qinteger))
+    {
+      if (BIGNUMP (value))
+	return sqlite3_bind_int64 (stmt, dex, bignum_to_intmax (value));
+      else
+	return sqlite3_bind_int64 (stmt, dex, XFIXNUM (value));
+    }
+  else if (EQ (type, Qfloat))
+    return sqlite3_bind_double (stmt, dex, XFLOAT_DATA (value));
+  else if (NILP (value))
+    return sqlite3_bind_null (stmt, dex);
+  else if (EQ (value, Qt))
+    return sqlite3_bind_int (stmt, dex, 1);
+  else if (EQ (value, Qfalse))
+    return sqlite3_bind_int (stmt, dex, 0);
+  else
+    return -1;
+}
+
+static Lisp_Object
+get_next_bind_candidate (int i, Lisp_Object *values)
+{
+  Lisp_Object value;
+  if (VECTORP (*values))
+    value = AREF (*values, i);
+  else
+    {
+      value = XCAR (*values);
+      *values = XCDR (*values);
+    }
+  return value;
+}
+
+/* Apparently, 999 was the max number of allowed params back in 2004
+   https://www.mail-archive.com/[email-masked]/msg05313.html */
+#define MAX_SQLITE_PARAMS 999
+
 /* Bind values in a statement like
    "insert into foo values (?, ?, ?)".  */
 static const char *
 bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
 {
   sqlite3_reset (stmt);
+  Lisp_Object orig_values = values;
+  int ret = SQLITE_MISMATCH;
   int len;
   if (VECTORP (values))
     len = ASIZE (values);
   else
     len = list_length (values);
 
+  bool indexes[MAX_SQLITE_PARAMS] = {0};
+  memset (indexes, 0, sizeof (indexes));
+
+  /* Deal with all named parameters first. */
   for (int i = 0; i < len; ++i)
     {
-      int ret = SQLITE_MISMATCH;
-      Lisp_Object value;
-      if (VECTORP (values))
-	value = AREF (values, i);
-      else
-	{
-	  value = XCAR (values);
-	  values = XCDR (values);
-	}
-      Lisp_Object type = Ftype_of (value);
-
-      if (EQ (type, Qstring))
-	{
-	  Lisp_Object encoded;
-	  bool blob = false;
+      Lisp_Object value = get_next_bind_candidate (i, &values);
+      if (NILP (Fkeywordp (value)))
+	continue;
+      char *param_name = SSDATA (encode_string (SYMBOL_NAME (value)));
+      int dex = sqlite3_bind_parameter_index (stmt, param_name);
+      if (dex == 0)
+	return "invalid named parameter";
+      indexes[dex] = true;
+      value = get_next_bind_candidate (++i, &values);
+      ret = bind_one_value (stmt, dex, value);
+      if (ret)
+	goto failed;
+    }
 
-	  if (SBYTES (value) == 0)
-	    encoded = value;
-	  else
-	    {
-	      Lisp_Object coding_system =
-		Fget_text_property (make_fixnum (0), Qcoding_system, value);
-	      if (NILP (coding_system))
-		/* Default to utf-8.  */
-		encoded = encode_string (value);
-	      else if (EQ (coding_system, Qbinary))
-		blob = true;
-	      else
-		encoded = Fencode_coding_string (value, coding_system,
-						 Qnil, Qnil);
-	    }
-
-	  if (blob)
-	    {
-	      if (SBYTES (value) != SCHARS (value))
-		xsignal1 (Qsqlite_error, build_string ("BLOB values must be unibyte"));
-	    ret = sqlite3_bind_blob (stmt, i + 1,
-				       SSDATA (value), SBYTES (value),
-				       NULL);
-	    }
-	    else
-	      ret = sqlite3_bind_text (stmt, i + 1,
-				       SSDATA (encoded), SBYTES (encoded),
-				       NULL);
-	}
-      else if (EQ (type, Qinteger))
+  /* Use unoccupied indexes for positional params. */
+  int pos_dex = 0;
+  values = orig_values;
+  for (int i = 0; i < len; ++i)
+    {
+      Lisp_Object value = get_next_bind_candidate (i, &values);
+      /* Skip keywords and their associated values. */
+      if (!NILP (Fkeywordp (value)))
 	{
-	  if (BIGNUMP (value))
-	    ret = sqlite3_bind_int64 (stmt, i + 1, bignum_to_intmax (value));
-	  else
-	    ret = sqlite3_bind_int64 (stmt, i + 1, XFIXNUM (value));
+	  if (!(VECTORP (values)))
+	    values = XCDR (values);
+	  i++;
+	  continue;
 	}
-      else if (EQ (type, Qfloat))
-	ret = sqlite3_bind_double (stmt, i + 1, XFLOAT_DATA (value));
-      else if (NILP (value))
-	ret = sqlite3_bind_null (stmt, i + 1);
-      else if (EQ (value, Qt))
-	ret = sqlite3_bind_int (stmt, i + 1, 1);
-      else if (EQ (value, Qfalse))
-	ret = sqlite3_bind_int (stmt, i + 1, 0);
-      else
-	return "invalid argument";
-
-      if (ret != SQLITE_OK)
-	return sqlite3_errmsg (db);
+      ret = -1;
+      while ((indexes[++pos_dex]) && pos_dex < MAX_SQLITE_PARAMS);
+      if (pos_dex == MAX_SQLITE_PARAMS - 1)
+	goto failed;
+      ret = bind_one_value (stmt, pos_dex, value);
+      if (ret)
+	break;
     }
-
+ failed:
+  if (ret == -1)
+    return "invalid argument";
+  if (ret != SQLITE_OK)
+    return sqlite3_errmsg (db);
   return NULL;
 }
 
diff --git a/test/src/sqlite-tests.el b/test/src/sqlite-tests.el
index a10dca9a0c9..e7b03bdb5bf 100644
--- a/test/src/sqlite-tests.el
+++ b/test/src/sqlite-tests.el
@@ -142,6 +142,80 @@ sqlite-param
       (sqlite-select db "select * from test4 where col2 = ?" [1])
       '(("foo" 1))))))
 
+(ert-deftest sqlite-keyword-params ()
+  (skip-unless (sqlite-available-p))
+  (let ((db (sqlite-open)))
+    (sqlite-execute
+     db "CREATE TABLE IF NOT EXISTS test4a (col1 TEXT, col2 NUMBER)")
+    (sqlite-execute db "INSERT INTO test4a VALUES (:a, :b)" '(:a "foo" :b 1))
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4a WHERE col2 = :a" '(:a 1))
+      '(("foo" 1))))
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4a WHERE col1 = :b" [:b "foo"])
+      '(("foo" 1))))
+
+    ;; Template specifiers reused.
+    (sqlite-execute
+     db (concat "CREATE TABLE IF NOT EXISTS test4b ("
+                " u, v, w, x, y, z, FOREIGN KEY(v) REFERENCES test4a(rowid)"
+                ")"))
+    ;; Here, t matches `col2' because it's a boolean and is coerced to 1.
+    (sqlite-execute db (concat "INSERT INTO test4b(u, v, w, x, y, z)"
+                               " SELECT :a, t.rowid, :b, :c, :d, :e"
+                               " FROM test4a as t "
+                               " WHERE t.col1 = :a AND t.col2 = :b")
+                    '(:a "foo" :b t :c false :d 3.14159 :e nil)) ; e is NULL
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4b WHERE v = :a AND w = :a" '(:a 1))
+      '(("foo" 1 1 0 3.14159 nil))))))
+
+(ert-deftest sqlite-mixed-params ()
+  (skip-unless (sqlite-available-p))
+  (let ((db (sqlite-open)))
+    (sqlite-execute
+     db "CREATE TABLE IF NOT EXISTS test4c (col1 TEXT, col2 NUMBER)")
+    (sqlite-execute db "INSERT INTO test4c VALUES (:a, ?)" '(:a "foo" 1))
+    (sqlite-execute db "INSERT INTO test4c VALUES (:s, ?)" '(1 :s "spam"))
+    (sqlite-execute db "INSERT INTO test4c VALUES (?, :b)" '("bar" :b 1))
+    (sqlite-execute db "INSERT INTO test4c VALUES (?, :z)" '(:z 1 "baz"))
+    (should (equal (sqlite-select db "SELECT * FROM test4c WHERE col2 = 1")
+                   '(("foo" 1) ("spam" 1) ("bar" 1) ("baz" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+                    '(:a "foo" 1))
+                   '(("foo" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+                    '(1 :a "foo"))
+                   '(("foo" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+                    '("bar" :b 1))
+                   '(("bar" 1))))
+    (should (equal (sqlite-select
+                    db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+                    '("bar" :b 1))
+                   '(("bar" 1))))
+
+    ;; Template comingled specifiers reused.
+    (sqlite-execute
+     db (concat "CREATE TABLE IF NOT EXISTS test4d ("
+                " u, v, w, x, y, z, FOREIGN KEY(v) REFERENCES test4c(rowid)"
+                ")"))
+    (sqlite-execute db (concat "INSERT INTO test4d(u, v, w, x, y, z)"
+                               " SELECT :a, t.rowid, :b, ?, ?, :e"
+                               " FROM test4c as t "
+                               " WHERE t.col1 = :a AND t.col2 = :b")
+                    '(:a "foo" :b t false 3.14159 :e nil)) ; e is NULL
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4d WHERE v = ? AND w = ?1" '(1))
+      '(("foo" 1 1 0 3.14159 nil))))))
+
 (ert-deftest sqlite-binary ()
   (skip-unless (sqlite-available-p))
   (let (db)
-- 
2.43.0