[sqlite] Crash when binding default column value

[Nathaniel Trellice]

Dear list members,

I'm trying to set a default value for a column in my "CREATE TABLE" statement. 
I really, really want to bind the default value (using sqlite3_bind_*) rather 
than expressing it in SQL text to avoid the following problems:

 * SQL injection attacks;
 * floating point value rounding in conversion to/from text;
 * how else can I set a default value for a binary blob column?


The SQL syntax tree for the "CREATE TABLE" statement at 
http://www.sqlite.org/lang_createtable.html suggests binding a default value is 
permitted. In particular"create-table-stmt:column-def:column-constraint:expr:" 
can be a bind parameter.


The example code, below, highlights the problem I'm having. The code creates a 
table with a bound default value for one of the two columns. The statement is 
prepared (no syntax error warning), and the value bound successfully. But upon 
inserting a row that requires using the default parameter, it crashes in 
function sqlite3ExprCodeTarget(). (Inserting a row with both column values 
explicitly set works fine.)


Tech details: using sqlite-amalgamation-3080600 on Centos 7, compiling with 
GCC4.8.2.


Am I making a silly mistake? Or, contrary to the documentation, is binding a 
default column value in a "CREATE TABLE" statement not possible? If it's not 
possible, can anyone suggest another way to achieve the goal of avoiding 
conversion ofdefault column values to SQL text?


Many thanks,

Nathaniel


Example code:



#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

int main(int argc, char* argv[])
{
  sqlite3 *db = NULL;
  sqlite3_stmt *pStmt = NULL;
  int c;

  /* Open database */
  c = sqlite3_open_v2(
    "test.db", &db, (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE), 0);
  if (c) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    exit(0);
  }

  /* Table creation SQL */
  const char *sql =
    "CREATE TABLE test_table ("
    "name     TEXT  DEFAULT ('joe'), "
    "interest TEXT  DEFAULT (?1))";

  /* Prepare SQL statement */
  c = sqlite3_prepare(db, sql, -1, &pStmt, NULL);
  if (c != SQLITE_OK) {
    fprintf(stderr, "Can't prepare statement: %s\n", sqlite3_errmsg(db));
    exit(0);
  }

  /* Bind SQL statement value */
  const char *golf = "golf";
  c = sqlite3_bind_text(pStmt, 1, golf, -1, SQLITE_STATIC);
  if (c != SQLITE_OK) {
    fprintf(stderr, "Can't bind statement: %s\n", sqlite3_errmsg(db));
    exit(0);
  }

  /* Evaluate SQL statement */
  c = sqlite3_step(pStmt);
  if (c != SQLITE_DONE) {
    fprintf(stderr, "Can't evaluate statement: %s\n", sqlite3_errmsg(db));
    exit(0);
  }

  /* Destroy prepared SQL statement */
  c = sqlite3_finalize(pStmt);
  if (c != SQLITE_OK) {
    fprintf(stderr, "Can't destroy statement: %s\n", sqlite3_errmsg(db));
    exit(0);
  }

  /* Insert row */
  const char *sql2 = "INSERT INTO test_table (name) VALUES ('jack');";
  char *errmsg = NULL;
  c = sqlite3_exec(db, sql2, NULL, NULL, &errmsg);
  if (c != SQLITE_OK) {
    fprintf(stderr, "Can't insert row: %s\n", errmsg);
    exit(0);
  }
  sqlite3_free(errmsg);

  /* Close database */
  c = sqlite3_close(db);
  if (c != SQLITE_OK) {
    fprintf(stderr, "Can't close database: %s\n", sqlite3_errmsg(db));
    exit(0);
  }

  return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users