On Jun 29, 2018, at 11:46 AM, Richard Hipp <d...@sqlite.org> wrote: > > On 6/29/18, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: >> >> Without adding all the necessary safe-guards to ensure that only valid >> data goes into the database, sqlite puts the using application at risk >> (security and stability) with its wishy-washy ways. > > Can you provide an example of a security of stability problem caused > by flexible typing?
The following is an answer to your challenge, not a feature request. I’d enable strong typing support in SQLite if it were available, but I started using SQLite knowing its stance on typing, so I can’t honorably demand it now. Nevertheless: 1. In defining a table schema, declare a column as UNSIGNED INTEGER; SQLite ignores the “UNSIGNED” and uses INTEGER affinity. 2. Store “-1FRED” in that column, for which SQLite uses TEXT affinity, so as to not lose any data. 3. Retrieve the value with sqlite3_column_int(), which forces a CAST to INT, resulting in -1, that being the longest prefix that is INT-like. 4. The application uses the value as an index into an array. If the application passes int* to sqlite3_column_int() to avoid compiler complaints, they’ll get a negative index. If they pass unsigned* instead, casting it to int* to placate the compiler, they get UINT_MAX on a 2’s complement machine, which will certainly crash the program when used as an array index. Either way, a security exploit is probably available. If your reaction is that the application shouldn’t have allowed input of “-1FRED” for an integer value, that’s true, but it would be nice if SQLite would backstop the application’s restrictions. The application tried to tell SQLite it wanted help enforcing its limits when giving the UNSIGNED attribute in declaring the table schema. If you say that the application shouldn’t have trusted the value it got from SQLite, why not? From the application programmer’s perspective, it’s now validated data. It is of course possible to work around all of this. The application programmer “just” has to write checks in layers closer to the end user, checks which are not strictly necessary with other DBMSes. People coming from those other DBMSes reasonably expect the data to be implicitly trustworthy once it is finally at rest. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
