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
