Re: [sqlite] Crash when binding default column value
At 21:53 24/09/2014, you wrote: > If the default cannot be represented exactly, its rounding will be the > least of the difficulties. Not always: in scientific applications I've had column values default to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc. My main gripe is when the value being read from a database isn't identical to the value written to the database. I've had problems in the past where conversion between double and text, e.g. sprintf() and atof(), isn't perfectly invertible even at 17+ significant figures. Perhaps this may only be an issue on some of the older platforms I've worked on or on traditional client/server architectures (with which I'm more familiar) where client and server can be different platforms. I haven't (yet) tested SQLite empirically, though I notice SQLite has its own atof() implementation sqlite3AtoF() which weakens my confidence that I'll get out exactly what float I put in represented as text. In my (admittedly limited) experience, IEEE754 implementations are transferable, when endian-flipped as appropriate. While it's only almost true that IEEE754 (which flavor?) are "transferable", you still seem to bark at the wrong tree. Like other contributors have already said, you definitely can't rely on a double stored value to represent exactly a real literal you supply. For instance as shown below, 0.75 = 0.5 + 0.25 has a bounded representation as fractional binary (1/2 + 1/4). On the contrary, 0.3 doesn't enjoy the same property and it would need an infinite string of decreasing powers of 2 to represent: its floating point representation is inexact. 0.75 is represented exactly in IEEE754 by X'3FE8' 0.3 is represented approximately in IEEE754 by X'3FD3' The finite set of reals (rational numbers in fact) that IEEE754 can represent is infinitesimally smaller than the set of reals, whatever finite number of bits the FP representation uses. Here's a real-world scenario where this could cause an issue (I've actually encountered this for real) Imagine an application writer who's instructed my library to use pi (say) as a column's default. When reading the value out of the database, they may want to test if the value equals the default... One way to get rid of that: use the output of select 3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679 which gives: 3.14159265358979 That value is now safe. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
On Wed, 24 Sep 2014 20:53:32 +0100 Nathaniel Trellice wrote: > > > You are doing CREATE TABLE statements based on text from an > > > untrusted user? Really? > > > My reaction exactly. > > > I'm writing a library so the safety of the input is out of my hands > and in that of the application writers who work 'above' me. I neither > want to burden them with the responsibility to escape strings nor > trust them to remember to do so. Fair enough. I can see why you'd want to use parameterized queries for that. And it looks as though you might get your wish. I guess if I were in your shoes I'd run the input through sqlite3_snprintf with %q, to safely double any quotes. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
On 24 Sep 2014, at 8:53pm, Nathaniel Trellice wrote: > Imagine an application writer who's instructed my library to use pi (say) as > a column's default. When reading the value out of the database, they may want > to test if the value equals the default Sorry, but you don't do this with REALs. Every programmer worth their salt knows you can't rely on that sort of thing. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
Hi Richard, James, > > > * SQL injection attacks; > > > > > > > You are doing CREATE TABLE statements based on text from an untrusted > > user? Really? > My reaction exactly. I'm writing a library so the safety of the input is out of my hands and in that of the application writers who work 'above' me. I neither want to burden them with the responsibility to escape strings nor trust them to remember to do so. In the myriad of uses planned for this library, it's a distinct possibility that there may be a UI with a default text value field filled-in by a user that trickles down directly into a database column default: there will certainly be occasions when a property's default value is specified by a user. Even if malicious injection attacks are unlikely via a text column default, seemingly benign default text, perhaps hard-coded by an unwitting application developer, could cause havoc (or, more likely, cause an exception to be thrown and a migration to fail) when not suitably escaped. This library includes limited support for schema evolution. SQLite's limited support for ALTER TABLE leads me to make many more CREATE TABLE calls than you might expect. > > > * floating point value rounding in conversion to/from text; > If the default cannot be represented exactly, its rounding will be the > least of the difficulties. Not always: in scientific applications I've had column values default to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc. My main gripe is when the value being read from a database isn't identical to the value written to the database. I've had problems in the past where conversion between double and text, e.g. sprintf() and atof(), isn't perfectly invertible even at 17+ significant figures. Perhaps this may only be an issue on some of the older platforms I've worked on or on traditional client/server architectures (with which I'm more familiar) where client and server can be different platforms. I haven't (yet) tested SQLite empirically, though I notice SQLite has its own atof() implementation sqlite3AtoF() which weakens my confidence that I'll get out exactly what float I put in represented as text. In my (admittedly limited) experience, IEEE754 implementations are transferable, when endian-flipped as appropriate. Here's a real-world scenario where this could cause an issue (I've actually encountered this for real) Imagine an application writer who's instructed my library to use pi (say) as a column's default. When reading the value out of the database, they may want to test if the value equals the default: if so, they can head up some pre-computed branch of code, otherwise they have to compute everything from scratch. Of course their equality comparison could (should?) have some tolerance threshold, but they'll come to me to complain when their code runs slow because I can't return exactly the same value they gave me. Okay, it was a 1 min patch. Nevertheless, you're right that representing a floating point value as text using a bunch of significant figures (I'd err on the side of caution and go with 17 or 18) will suffice: it will have to. > > > * how else can I set a default value for a binary blob column? >http://www.sqlite.org/lang_expr.html >"BLOB literals are string literals containing hexadecimal data > and preceded by a single "x" or "X" character. Example: X'53514C697465'" Thanks for the pointer to Blob literals. I hadn't spotted them. If you think I'm being pedantic/overly-preemptive, you're right: These issues are unlikely to cause anyone headaches. My motivation for wanting to bind default values is a combination of: 1. An artificial (unrealistic?) attempt to maintain clean separation between manipulation of database structure and database content: I'm happy to use sql/text for the former but want to use all-binary transfer for the latter. So far, I've been able to adhere to this, but life's never so clean: default values fall nicely in the grey area: they start as database structure (part of the schema), then end up as database content. 2. Binding values makes these concerns, however poxie, go away. Nathaniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
On Wed, 24 Sep 2014 09:49:36 -0400 Richard Hipp wrote: > > * SQL injection attacks; > > > > You are doing CREATE TABLE statements based on text from an untrusted > user? Really? My reaction exactly. > > * floating point value rounding in conversion to/from text; If the default cannot be represented exactly, its rounding will be the least of the difficulties. > > * how else can I set a default value for a binary blob column? http://www.sqlite.org/lang_expr.html "BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'" HTH. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
On Wed, Sep 24, 2014 at 7:36 AM, Nathaniel Trellice
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
> #include
> #include
>
> 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
Re: [sqlite] Crash when binding default column value
On 24 Sep 2014, at 1:33pm, Nathaniel Trellice wrote:
> Without the INSERT call, my test code runs through without crashing.
> Attempting the INSERT call in the shell tool crashes it too. Following your
> advice, the output of the .schema command looks dodgy:
>
> CREATE TABLE test_table (name TEXT DEFAULT ('joe'), interest TEXT DEFAULT
> (?1));
>
> I'm an SQLite newbie, though, so maybe that's right. Or perhaps bound
> parameters aren't 'resolved' in a "CREATE TABLE" statement?
You got it right. As you can see from the above, SQLite did not make the
substitution for your bound value and the '?1' made it all the way to the table
definition. You should, of course, be seeing your 'golf' value there instead.
I'm going to have to let someone else tell you whether you should or shouldn't
be able to bind DEFAULT values. I don't know.
But having allowed that to happen SQLite now has a definition for a table, and
that definition has bad syntax. And that bad syntax is causing SQLite to
crash. And that is a no-no and will be fixed. I have no idea how it'll be
fixed but it will. Thanks for posting the details of your problem and a clear
way to reproduce it.
In the meantime if you need your program to set default values for table
columns at runtime you'll have to do it by editing the command's text instead
of using binding. Which you already know is bad for reasons you listed in your
first post. Sorry for the inconvenience.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
I think that the diagrams on http://www.sqlite.org/lang_createtable.html are
the generic parsing and construction rules for "expr" everywhere an "expr" can
be used. The text description further down the page describes restrictions and
usage particular to the CREATE TABLE statement itself.
Under COLUMN DEFINITIONS you have:
The DEFAULT clause specifies a default value to use for the column if no value
is explicitly provided by the user when doing an INSERT. If there is no
explicit DEFAULT clause attached to a column definition, then the default value
of the column is NULL. An explicit DEFAULT clause may specify that the default
value is NULL, a string constant, a blob constant, a signed-number, or any
constant expression enclosed in parentheses. An explicit default value may also
be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is
considered constant provided that it does not contain any sub-queries, column
or table references, or string literals enclosed in double-quotes instead of
single-quotes.
note the "constant expression". The type of "constant" is stated, and that it
may also be a constant expression (meaning an expression of constants). 45.7
given literally is a constant. sin(45.7) is a constant expression. Since ?
is not a constant, then an expression containing ? is not a constant
expression. Perhaps it could be more explicit by saying that bound values are
not permitted.
Under SQL Data Constraints you will also see that:
A CHECK constraint may be attached to a column definition or specified as a
table constraint. In practice it makes no difference. Each time a new row is
inserted into the table or an existing row is updated, the expression
associated with each CHECK constraint is evaluated and cast to a NUMERIC value
in the same way as a CAST expression. If the result is zero (integer value 0 or
real value 0.0), then a constraint violation has occurred. If the CHECK
expression evaluates to NULL, or any other non-zero value, it is not a
constraint violation. The expression of a CHECK constraint may not contain a
subquery.
Nothing in the CREATE TABLE (or any CREATE) can contain a bound value (well, I
suppose the parser could render it into text and insert that value literally,
but at the moment it does not). This is because the schema is stored as the
text of the create statements, so the value of that variable must be available
each time the constraint parsed when DML is parsed against the definition.
>-Original Message-
>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
>#include
>#include
>
>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) {
>fpr
Re: [sqlite] Crash when binding default column value
On Wed, Sep 24, 2014 at 8:33 AM, Nathaniel Trellice
wrote:
> Attempting the INSERT call in the shell tool crashes it too. Following
> your advice, the output of the .schema command looks dodgy:
>
> CREATE TABLE test_table (name TEXT DEFAULT ('joe'), interest TEXT DEFAULT
> (?1));
>
> I'm an SQLite newbie, though, so maybe that's right.
>
SQLite should never crash. This is a bug. We've already written the
ticket (http://www.sqlite.org/src/tktview/78c0c8c3c9f7c) and are working on
a fix.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
Hi Simon,
Thanks for the quick reply.
Without the INSERT call, my test code runs through without crashing. Attempting
the INSERT call in the shell tool crashes it too. Following your advice, the
output of the .schema command looks dodgy:
CREATE TABLE test_table (name TEXT DEFAULT ('joe'), interest TEXT DEFAULT
(?1));
I'm an SQLite newbie, though, so maybe that's right. Or perhaps bound
parameters aren't 'resolved' in a "CREATE TABLE" statement?
Thanks again,
Nathaniel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
My guess would be that finalizing the create table statement makes the bound
value go out of scope and thus be unavailable to the insert statement.
Bound values reside somewhere in the internal prepared statement structure and
do not get copied into the database file, even if they happen to be default
column values.
-Ursprüngliche Nachricht-
Von: Nathaniel Trellice [mailto:napt...@yahoo.co.uk]
Gesendet: Mittwoch, 24. September 2014 13:36
An: sqlite-users@sqlite.org
Betreff: [sqlite] Crash when binding default column value
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
#include
#include
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
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when binding default column value
On 24 Sep 2014, at 12:36pm, Nathaniel Trellice wrote: > 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.) Can you split your program into two ? Use your code to create the table in the first place but not do the INSERT. Then use the SQLite shell tool to open the resulting database and look at the schema (perhaps use the command '.schema'). Check to see that the schema is what you'd expect it to be. Then use the SQLite shell tool to INSERT some lines and see whether the shell tool crashes. If the shell tool crashes we have a 100% repeatable case where the SQLite development team have all the source code. It would be very easy to debug. If the shell tool doesn't crash then the problem is probably somewhere in your own code rather than in SQLite. This doesn't mean nobody will help you, but it makes it easier to see where the problem is. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Crash when binding default column value
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
#include
#include
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
