Re: [sqlite] Bug in sqlite3_step
SQLite3 also needs to know the identifiers of schema elements at statement prep time. It might be nice to have a variant of sqlite3_prepare_v2() that takes a varargs list of parameters which must be identifiers, and then have a syntax for referring to identifier parameters as opposed to value parameters. But this would just be a glorified (if safer) variant of sqlite3_mprintf() -- for apps that allow users to manipulate the schema this could actually be a good improvement. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] When to call VACUUM - revisited
Hi List, I've read what I could find about the issue, this thread: http://thread.gmane.org/gmane.comp.db.sqlite.general/57770/focus=57775 pretty much gathers the positions, ranging from - don't - don't worry - don't expect to much - let the user decide The last is closest to my approach - even though the evidence for VACUUM Miracles is sketchy. However, I have a problem here: Q: If I can't decide when to VACUUM, how should my user? The application: We already have a maintenance operation where VACUUM would be a good fit. However, VACUUM is by far requiring the most time, and with most uncertain results (i.e. whether or not it improves subsequent performance). We use SQLite as applicaiton data format, the user is encouraged to work with the files for sharing data etc. We run with auto-vaccum ON, since we frequently add and remove large-ish blocks, and reclaiming free space seems to be important for users. As one reply here states, 'OS has defrag disk, Outlook has compact folders'. However, they also have rough indicators, whether or not it's necessary (fragmentation or a recommendation when closing Outlook) Q: Are there any useful indicators to suggest a VACUUM? (Or, to reverse it: indicators that show VACUUM is not necessary?) Since we plan to run with auto-vacuum, free size isn't an indicator. I've also considered brewing something with last-vacuum-date and a change counter. Any ideas? ph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to call VACUUM - revisited
On 4 Jul 2012, at 10:54 AM, Hauptmann Peter hauptma...@yahoo.com wrote: However, VACUUM is by far requiring the most time, and with most uncertain results (i.e. whether or not it improves subsequent performance). VACUUM exists only to release unused space from the database file. If you have been entering new data and not deleted old data it will do nothing usefull unless your OS is sensitive to fragmentation (i.e. Windows). Provide your users with a way to do it so you can, during a support call, decide it might help. Don't bother doing it on a regular basis. Simon. -- Sent from my iPhone ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_step
Nico Williams n...@cryptonector.com wrote: SQLite3 also needs to know the identifiers of schema elements at statement prep time. It might be nice to have a variant of sqlite3_prepare_v2() that takes a varargs list of parameters which must be identifiers, and then have a syntax for referring to identifier parameters as opposed to value parameters. That doen't make much sense. The query plan for select * from table1 where col1=? may be completely different from one for select * from table2 where col2=?. What exactly do you expect sqlite3_prepare_v2 to prepare, if table and column could vary afterwards? Also, what are sqlite3_column_count, sqlite3_column_decltype et al supposed to return? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to call VACUUM - revisited
On 4 Jul 2012, at 10:54am, Hauptmann Peter hauptma...@yahoo.com wrote: However, they also have rough indicators, whether or not it's necessary (fragmentation or a recommendation when closing Outlook) Q: Are there any useful indicators to suggest a VACUUM? (Or, to reverse it: indicators that show VACUUM is not necessary?) I forgot the more useful information: http://www.sqlite.org/pragma.html#pragma_freelist_count PRAGMA freelist_count; Return the number of unused pages in the database file. The bigger this is, the more point there is in doing a VACUUM. Unless the next thing you're going to do is add more data to the database in which case the VACUUM is a waste of time. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_step
On Wed, Jul 04, 2012 at 01:09:01AM -0500, Nico Williams scratched on the wall: But this would just be a glorified (if safer) variant of sqlite3_mprintf() -- for apps that allow users to manipulate the schema this could actually be a good improvement. The sqlite3_*printf() family supports the %w option specifically for the safe formatting identifiers. Like the %q option, you need to include the quotes in your string literal. So your modified prepare would look something like this: sql_str = sqlite3_mprintf( DROP TABLE \%w\, table_name ); sqlite3_prepare_v2( db, sql_str, -1, stmt, NULL ); sqlite3_free( sql_str ); -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_step
On Wed, Jul 04, 2012 at 09:06:16AM -0400, Igor Tandetnik scratched on the wall: Nico Williams n...@cryptonector.com wrote: SQLite3 also needs to know the identifiers of schema elements at statement prep time. It might be nice to have a variant of sqlite3_prepare_v2() that takes a varargs list of parameters which must be identifiers, and then have a syntax for referring to identifier parameters as opposed to value parameters. That doen't make much sense. Yes it does. The identifiers would be baked into the statement before it is prepared. He's not trying to alter the identifiers after the statement is prepared, he's trying to prevent SQL-injection attacks while the statement is prepared. The #1 rule to prevent SQL-injection attacks is, Never let user-generated strings pass through the SQL parser. Statement re-use is nice, but the real value in using SQL statements and parameters is that you avoid passing data values through the SQL parser, making SQL-injection attacks impossible. 99% of SQL-injection attacks could be avoided if the programmer had only used SQL parameters rather than string concatenation. It's also why sqlite3_exec() should really include a vararg option, so that one could pass in values outside the SQL string itself. Unfortunately, you can't use parameters for everything. If you get in a situation where you need to use a user-defined table name, parameters won't help you. The (distant) #2 rule to prevent SQL-injection attacks is, Sanitize user-generated strings before they pass through the SQL parser. The issue with that is that too many programmers think themselves clever and smart, so they write their own sanitizer, and they do so poorly. Hence the popularity of tried, true, and tested string sanitizers being built into SQL libraries. That's what Nico is looking for. Thankfully, SQLite includes this functionality, just not in the _prepare() functions. SQLite supports several extensions to the standard printf() syntax in the sqlite3_*printf() family of functions. Both %q and %Q can be used for values, while %w can be used for identifiers. The sqlite3_*printf() functions will properly quote and sanitize any value in the generated string. There is also a %z and %p, but they're not really important for this discussion. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_step
On Wed, Jul 04, 2012 at 08:29:33AM -0500, Jay A. Kreibich scratched on the wall: On Wed, Jul 04, 2012 at 01:09:01AM -0500, Nico Williams scratched on the wall: But this would just be a glorified (if safer) variant of sqlite3_mprintf() -- for apps that allow users to manipulate the schema this could actually be a good improvement. The sqlite3_*printf() family supports the %w option specifically for the safe formatting identifiers. ...and someone just pointed out that %w isn't documented on the SQLite site: http://sqlite.org/c3ref/mprintf.html Sorry about that. I'm not sure if that's an oversight in the docs, or if it is an undocumented feature. See Using SQLite http://shop.oreilly.com/product/9780596521196.do, Apdx G, p474-475 for more info. It seems these pages are included in Amazon's Look Inside feature (at least for me): http://www.amazon.com/Using-SQLite-Jay-A-Kreibich/dp/0596521189/. Or see the SQLite source. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?
Igor Tandetnik itandetnik-fh9draxc...@public.gmane.org writes: It doesn't matter. The whole point is that the key is constructed in such a way that both procedures would arrive at the same result. That's really what was at the heart of my question: If separate value-by-value was necessary, then I would conclude that the concatenated key would /not/ yield the corrected result by comparing the whole thing in one operation. I was having trouble convincing myself that the single comparison would always work. I was looking for a counterexample, where a shorter value's end and the beginning the following value could be mistakenly compared against a single longer value. The language used in the referenced Wiki page added further to my confusion, so I thought it best to ask for clarification. Studying the encoding more carefully, I see that the end of one value will always sort earlier than any intermediate byte in another value. That numbers can never contain a zero byte is an easy-to-miss detail. A couple more nits on that page: In the second paragraph, we find: , | until a difference if found ` ^ | +--+ | s/if/is/ The seventh paragraph reads as follows: , | The first byte of a key past the table number will be in the range of | 0x05..0x0f if ascending or 0xf0..0xfa if descending. This leaves large | chunks of key space available for other uses. For example, the | three-byte key 0x00 0x00 0x01 stores the schema cookie for the database | as a 64-bit big-endian integer. ` The part that says the first byte will be valued between 5 and 15 (decimal) for ascending values doesn't agree with the table at the end, where we see leading bytes in the range 0x05 to 0x25, or 5 to 37 decimal. Am I comparing different things here? Finally, to restate an inquiry from my earlier message, which I had some trouble sending properly: I don't understand why the example entries for values 99.0, 99.01, and 99.0001 have first bytes of 0xb4 and 0xb5, respectively. If we take 99 and use it as X in the 2*X+0 formula, shouldn't we wind up with 198, or 0xC6? Likewise, for the second two examples, I'd expect 2*99+1 to yield 199, or 0xC7. (The later entry for has bytes 0xC7 and 0xC6, which match my expectation.) -- Steven E. Harris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Standalone LUA regex that can be used in sqlite
Hello ! I did a modification to the LUA regex code to allow using it without LUA dependency so it can be used with sqlite as regex function. It's very light and small thus a good candidate to be included on sqlite. If you are interested on it you can download it here http://code.google.com/p/lua-regex-standalone/ , it's released under the same licence of LUA (MIT license). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange issue with sqlite 3.7.9
Due to hardware problems with my Debian Stable server, I have just upgraded to Ubuntu-Server 12.04. I have installed sqlite3 and when I ask it the version (with .version) it replies SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e Which exactly complies with the sqlite web site for this release. Sometime ago I reported a problem with 3.7.10 referenced here http://www.sqlite.org/src/info/b7c8682cc1 Using the same database (which I would prefer not to publish because its full of personal financial information - I already gave Richard Hipp a randomised copy in respect of the last bug) I have experienced a problem shown below. I am not sure this is the same problem as before (its very similar) but that was reported as being introduced with changes introduced in 3.7.10 select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain = 'Hartley' AND ((t.src = a.name and t.srccode = c.id) ) GROUP BY c.id; Produces output, where as select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain = 'Hartley' AND ((t.src = a.name and t.srccode = c.id) OR (t.dst = a.name and t.dstcode = c.id )) GROUP BY c.id; does not even though ONLY added an OR clause within a bracketed AND clause Just to confuse the issue dfxaction (but not the other tables) is a view - defined as below (and I think this is where the similarity to the other bug comes in). If I replace that with xaction (the real table its based on) then the second select above does produce expected output. CREATE VIEW dfxaction AS SELECT t.id,t.date,t.version, src, srccode, dst, dstcode,t.description, rno, repeat, CASE WHEN t.currency = 'GBP' THEN t.amount WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN t.srcamount WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN t.dstamount ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER) END AS dfamount FROM xaction AS t LEFT JOIN account AS sa ON t.src = sa.name LEFT JOIN account AS da ON t.dst = da.name LEFT JOIN currency ON t.currency != 'GBP' AND (t.srcamount IS NULL OR sa.currency != 'GBP') AND (t.dstamount IS NULL OR da.currency != 'GBP') AND t.currency = currency.name; ALSO just to confirm - I repeated the same experiment on sqlite3 version 3.7.13 (Debian unstable version) and the second query performs perfectly I am mentioning this here because the earlier bug was supposed to have been caused by a change made by 3.7.10, whereas this is 3.7.9 and given its the version of choice in ubuntu it might be better to clarify whether there is a problem there or not. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange issue with sqlite 3.7.9
On 07/04/2012 08:26 PM, Alan Chandler wrote: Due to hardware problems with my Debian Stable server, I have just upgraded to Ubuntu-Server 12.04. I have installed sqlite3 and when I ask it the version (with .version) it replies SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e Which exactly complies with the sqlite web site for this release. Sometime ago I reported a problem with 3.7.10 referenced here http://www.sqlite.org/src/info/b7c8682cc1 The commit referenced by that page: http://www.sqlite.org/src/info/b23ae131874bc5c621f0 went into 3.7.9. So the problem was probably introduced in 3.7.9, not 3.7.10. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange issue with sqlite 3.7.9
On 04/07/12 18:52, Dan Kennedy wrote: On 07/04/2012 08:26 PM, Alan Chandler wrote: Due to hardware problems with my Debian Stable server, I have just upgraded to Ubuntu-Server 12.04. I have installed sqlite3 and when I ask it the version (with .version) it replies SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e Which exactly complies with the sqlite web site for this release. Sometime ago I reported a problem with 3.7.10 referenced here http://www.sqlite.org/src/info/b7c8682cc1 The commit referenced by that page: http://www.sqlite.org/src/info/b23ae131874bc5c621f0 went into 3.7.9. So the problem was probably introduced in 3.7.9, not 3.7.10. Indeed - I just tried the test case in that ticket and in fact demonstrated that the bug is in 3.7.9 -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_step
On Wed, Jul 4, 2012 at 8:06 AM, Igor Tandetnik itandet...@mvps.org wrote: Nico Williams n...@cryptonector.com wrote: SQLite3 also needs to know the identifiers of schema elements at statement prep time. It might be nice to have a variant of sqlite3_prepare_v2() that takes a varargs list of parameters which must be identifiers, and then have a syntax for referring to identifier parameters as opposed to value parameters. That doen't make much sense. The query plan for select * from table1 where col1=? may be completely different from one for select * from table2 where col2=?. What exactly do you expect sqlite3_prepare_v2 to prepare, if table and column could vary afterwards? Also, what are sqlite3_column_count, sqlite3_column_decltype et al supposed to return? Precisely, which is why any identifiers (table names, column names) have to be known at statement prep time. But using parametrized queries adds some safety, so it makes sense to me to have two types of parameters: those which must be bound at statement prep time, and those that can be bound later. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange issue with sqlite 3.7.9
On Wed, Jul 4, 2012 at 3:05 PM, Alan Chandler a...@chandlerfamily.org.ukwrote: The commit referenced by that page: http://www.sqlite.org/src/info/b23ae131874bc5c621f0 went into 3.7.9. So the problem was probably introduced in 3.7.9, not 3.7.10. Indeed - I just tried the test case in that ticket and in fact demonstrated that the bug is in 3.7.9 Does that mean that the problem is fixed by http://www.sqlite.org/src/info/0dc4cb9355 and does not exist in recent releases of SQLite? Or are you saying that this is a new problem that needs to be addressed. If the latter, I'm going to need you to send me a database again so that I can reproduce the problem, because I did keep the one you sent last time. -- Alan Chandler http://www.chandlerfamily.org.**uk http://www.chandlerfamily.org.uk __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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
