On Wed, Sep 24, 2014 at 7:36 AM, Nathaniel Trellice <napt...@yahoo.co.uk> wrote:
> 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; > You are doing CREATE TABLE statements based on text from an untrusted user? Really? If you really need to do that, construct the CREATE TABLE statement using the sqlite3_mprintf() family of string formatting functions and use %Q to substitute text literals. %Q will automatically add the surrounding '..' marks and escape internal ' characters, correctly. char *zSql = sqlite3_mprintf("CREATE TABLE t1(a, b DEFAULT(%Q))", zStringFromUser); sqlite3_exec(db, zSql, 0, 0, 0); sqlite3_free(zSql); > * floating point value rounding in conversion to/from text; > Just give 15 or 16 digits of precision. > * how else can I set a default value for a binary blob column? > Using a blob literal. Ex: x'a5b4c3'. See the output of: "SELECT quote(randomblob(10));" > > > 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 text has been clarified to say that bound parameters are not permitted in a DEFAULT clause. > > > 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. > The segfault problem has now been fixed in trunk. You'll get an error message instead. Legacy databases that already have parameters sitting in their sqlite_master tables will see those parameters be silently converted into NULL if they are ever used. > > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users