On Wed, Sep 24, 2014 at 7:36 AM, Nathaniel Trellice <[email protected]>
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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
