pragma application_written_by=coder; -vs the default, which is-
pragma application_written_by=programmer; ;-) All of the issues raised are "application" problems, not database problems. Clearly if you retrieved a value from the database and want to use it as an index you have to do bounds checking. The ability to forsee that the world may not be entirely as you expect is the root of the difference between a mere coder and a professional software programmer. (Not to mention, it was the same error that allowed the errant value to get into the database in the first place). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Warren Young >Sent: Friday, 29 June, 2018 13:00 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
