[sqlite] Bug or some misunderstanding?

[Support]

Hi
I have a database which has an entry "USE" in a table called airports 
with column LocationID.

When I call
sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID 
like "USE%";'

I get correct result.

But when I call
sqlite3 -line ~/Desktop/maps.db 'select * from airports where 
LocationID=="USE";'


I do not get any result.

When I call
sqlite3 -line ~/Desktop/maps.db 'select LocationID from airports;'

I do see that "USE" is in there among many other entries.

It only happens with "USE". Does not happen with "BOS" etc.

Thanks
Z
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TCL Test failures on ARM

[Bk]

Hi,

Thank you all for the reply.

i am still wondering why setting DSQLITE_DISABLE_LFS=1 had no effect on the
code when we have a clear #ifndef ? , 

1) if my application does not need LFS , how do i really make it here
(system uses : Linux nitrogen6x 3.0.15-ts-armv7l) ?

i was getting sizeof(off_t) to 8 in robust_ftruncate and 4 in ts_ftruncate

i was expecting DSQLITE_DISABLE_LFS=1 should make  sizeof(off_t) to 4 in
robust_ftruncate , but it didnt.

solution tried: 

2) Later i added below code in the begining of test_syscall.c which made
ts_ftruncate() 's  sizeof(off_t)  to change to 8. 

#ifndef SQLITE_DISABLE_LFS
# define _LARGE_FILE   1
# ifndef _FILE_OFFSET_BITS
#   define _FILE_OFFSET_BITS 64
# endif
# define _LARGEFILE_SOURCE 1
#endif

3) Another solution i tried was setting -D_FILE_OFFSET_BITS=64 while
building 





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67995.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug or some misunderstanding?

[Donald Griggs]

Hello, "Z",

Have you made sure you don't have a trailing space or other invisible
character at the end of the field?

As a first step, you might try a query such as the following:
   * select   '(' || LocationID || ')' , *from airports where
LocationID like 'USE%'*

Maybe this helps,
Donald Griggs

On Tue, Apr 2, 2013 at 7:54 PM, Support  wrote:

> Hi
> I have a database which has an entry "USE" in a table called airports with
> column LocationID.
> When I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID
> like "USE%";'
> I get correct result.
>
> But when I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where
> LocationID=="USE";'
>
> I do not get any result.
>
> When I call
> sqlite3 -line ~/Desktop/maps.db 'select LocationID from airports;'
>
> I do see that "USE" is in there among many other entries.
>
> It only happens with "USE". Does not happen with "BOS" etc.
>
> Thanks
> Z
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug or some misunderstanding?

[Dominique Devienne]

On Wed, Apr 3, 2013 at 1:54 AM, Support  wrote:

> sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID
> like "USE%";'
> I get correct result.
>
> But when I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where
> LocationID=="USE";'
>

SQL uses =, not == (and string literals are in single-quotes, just in
case). --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug or some misunderstanding?

[Richard Hipp]

On Tue, Apr 2, 2013 at 7:54 PM, Support  wrote:

> Hi
> I have a database which has an entry "USE" in a table called airports with
> column LocationID.
> When I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID
> like "USE%";'
> I get correct result.
>
> But when I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where
> LocationID=="USE";'
>

Double-quotes means use the value in the column named "USE".  If you want
the string literal, use single-quotes.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Hello,
I have a question about user defined function.

When I make user defined function, that function has argument count and
values only.
Is there any way that I can recognize there arguments are generated from
dynamic binding (ex: "?") or static string?

For following examples, there are two different cases.

ex1)
SELECT * FROM myTable WHERE myFunc(col1, "test string");

ex2)
SELECT * FROM myTable WHERE myFunc(col1, ?);

As you can see here, second parameter is changed.
But, myFunc() gets same values for each case.

For more information,
I just made some functions that handling files path.
But, if file name includes "Special characters(ex: '"') " or "Unicode" and
it is used for myFunc()
then it makes "Syntax error" error code and execution is failed.
What I want to do is to make an "WARNING" message if parameter is not
dynamic binding value for developers who would use myFunc().

Best regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Richard Hipp]

On Wed, Apr 3, 2013 at 8:58 AM, Yongil Jang  wrote:

> Is there any way that I can recognize there arguments are generated from
> dynamic binding (ex: "?") or static string?
>

No.  Applications-defined functions are call-by-value, as in C.  If you
have a C function, you cannot tell if the parameters came from variables,
expressions, or literals.  In the same way, SQLite application defined
functions have no means of tracing the original of parameter values.

-- 
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] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Thank you!

I may need to make plan B.
2013. 4. 3. 오후 10:04에 "Richard Hipp" 님이 작성:

> On Wed, Apr 3, 2013 at 8:58 AM, Yongil Jang  wrote:
>
> > Is there any way that I can recognize there arguments are generated from
> > dynamic binding (ex: "?") or static string?
> >
>
> No.  Applications-defined functions are call-by-value, as in C.  If you
> have a C function, you cannot tell if the parameters came from variables,
> expressions, or literals.  In the same way, SQLite application defined
> functions have no means of tracing the original of parameter values.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Simon Slavin]

On 3 Apr 2013, at 2:08pm, Yongil Jang  wrote:

> Thank you!
> 
> I may need to make plan B.

Create a third parameter that tells your function what to do.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Igor Tandetnik]

On 4/3/2013 8:58 AM, Yongil Jang wrote:

For more information,
I just made some functions that handling files path.
But, if file name includes "Special characters(ex: '"') " or "Unicode" and
it is used for myFunc()
then it makes "Syntax error" error code and execution is failed.
What I want to do is to make an "WARNING" message if parameter is not
dynamic binding value for developers who would use myFunc().


If the string literal is syntactically invalid, any syntax errors would 
be reported at the time the statement is prepared. Naturally, a 
statement with syntax errors cannot be executed, so your function 
wouldn't be called in the first place; of course, if it's not called, it 
can't issue any warnings.


If your function is running, this means it's been given a valid string, 
whether as a string literal or a bound parameter.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New Optimizations in 3.7.16 -- Explain please?

[Patrick Herbst]

> Can someone give me a case where the new changes make a difference in
> relation to the following two changes:
>
> (from http://www.sqlite.org/releaselog/3_7_16.html)
> - Enhance virtual tables so that they can potentially use an index
> when the WHERE clause contains the IN operator.
> - Allow indices to be used for sorting even if prior terms of the
> index are constrained by IN operators in the WHERE clause.
>
> I use SELECT's using IN's... but I'm not seeing any improved performance.
>
> Anyone know exactly what is enhanced?  or how it now allows indices to
> be used where they couldn't be before?
>
> Thanks!!

Anyone have any use-cases to show the new optimizations?  Please?

Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug or some misunderstanding?

[Jay A. Kreibich]

On Wed, Apr 03, 2013 at 02:28:07PM +0200, Dominique Devienne scratched on the 
wall:
> On Wed, Apr 3, 2013 at 1:54 AM, Support  wrote:
> 
> > sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID
> > like "USE%";'
> > I get correct result.
> >
> > But when I call
> > sqlite3 -line ~/Desktop/maps.db 'select * from airports where
> > LocationID=="USE";'
> >
> 
> SQL uses =, not == (and string literals are in single-quotes, just in
> case). --DD

  True, although SQLite supports several additional C style operators,
  including "==", to keep us all from going crazy.  Perhaps not a good
  habit to get into, but perfectly valid for SQLite:

  http://www.sqlite.org/lang_expr.html

  Note that there are two variations of the equals and not equals
  operators. Equals can be either = or ==. The non-equals operator
  can be either != or <>.


   -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] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Thank you, Simon and Igor.

I will investigate about your opinion, as you mentioned.

In general, if parameter string contains alphabets only, it doesn't make
any problems.

However, I couldn't check that my function is used correctly for every
applications.
Some developers don't know why does it fails when using special characters
and applications can be packaged with hidden issues.

For this reason, I was looking for some solutions that I can send warning
messages to application developers if they use plain text without binding
arguments.

Best regards,
Yongil jang.
2013. 4. 3. 오후 10:13에 "Igor Tandetnik" 님이 작성:

> On 4/3/2013 8:58 AM, Yongil Jang wrote:
>
>> For more information,
>> I just made some functions that handling files path.
>> But, if file name includes "Special characters(ex: '"') " or "Unicode" and
>> it is used for myFunc()
>> then it makes "Syntax error" error code and execution is failed.
>> What I want to do is to make an "WARNING" message if parameter is not
>> dynamic binding value for developers who would use myFunc().
>>
>
> If the string literal is syntactically invalid, any syntax errors would be
> reported at the time the statement is prepared. Naturally, a statement with
> syntax errors cannot be executed, so your function wouldn't be called in
> the first place; of course, if it's not called, it can't issue any warnings.
>
> If your function is running, this means it's been given a valid string,
> whether as a string literal or a bound parameter.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug or some misunderstanding?

[Dominique Devienne]

On Wed, Apr 3, 2013 at 1:54 AM, Support  wrote:

> Hi
> I have a database which has an entry "USE" in a table called airports with
> column LocationID.
> When I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID
> like "USE%";'
> I get correct result.
>
> But when I call
> sqlite3 -line ~/Desktop/maps.db 'select * from airports where
> LocationID=="USE";'
>
> I do not get any result.
>

As you can see below, it works if you put double-quotes around the
statement. Works on Linux too.

By wrapping the statement in single-quotes, and attempting to "escape" the
single-quotes around USE by doubling them, you are in effect closing the
previous single-quote, and opening a new one, but no single-quote is left
for SQLite to process. It's as if you wrote where locid = USE, and SQLite
errors out with "Error: no such column: USE". So I think it's a
shell-specific quote escaping issue.

C:\Users\DDevienne>sqlite3 foo.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table airports (locid text primary key);
sqlite> insert into  airports values ('USE'), ('IAH');
sqlite> .q

C:\Users\DDevienne>sqlite3 -line foo.db "select * from airports where locid
like 'USE%';"
locid = USE

C:\Users\DDevienne>sqlite3 -line foo.db "select * from airports where locid
= 'USE';"
locid = USE

C:\Users\DDevienne>sqlite3 -line foo.db "select * from airports where locid
== 'USE';"
locid = USE


> When I call
> sqlite3 -line ~/Desktop/maps.db 'select LocationID from airports;'
>
> I do see that "USE" is in there among many other entries.
>
> It only happens with "USE". Does not happen with "BOS" etc
>

Frankly, that's surprising. If that's true, you need to "show us the data".
Perhaps there's invisible whitespace after USE that like 'USE%' accepts,
whereas = 'USE' does not. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Tiago Rodrigues]

Hello all,

I'm writing a small simulation app and for it I would like to use SQLite3
as an application file format, as suggested by the "Appropriate uses for
SQLite" page in sqlite.org.  More specifically, the page suggests calling
BEGIN TRANSACTION when opening a file and calling COMMIT when saving it
again, or ROLLBACK if closing it without saving.  Simple enough, up until
the point where I want to implement a "Save As..." menu option, where the
current state of the application is saved to a new file (new database) with
the changes, while the changes in the current file are rolled back.

For that, the simplest idea would be to use the online backup family of
functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the
database, calling COMMIT on the backup and ROLLBACK on the original.
Naturally, that doesn't work, as you can't back up a database in the middle
of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY.

That being said, has anyone on the list encountered (and hopefully solved)
this particular problem?  This probably means I have to scrap the idea of
holding a transaction through the editing of the file, but then should I
copy the database to a memory-based db?  Any particular tips or caveats?

I appreciate the attention,

  -Tiago

-- 
In those days, in those distant days, in those nights, in those remote
nights, in those years, in those distant years...
  - Gilgamesh, Enkidu and the Underworld
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Nico Williams]

On Wed, Apr 3, 2013 at 4:11 PM, Tiago Rodrigues  wrote:
> I'm writing a small simulation app and for it I would like to use SQLite3
> as an application file format, as suggested by the "Appropriate uses for
> SQLite" page in sqlite.org.  More specifically, the page suggests calling
> BEGIN TRANSACTION when opening a file and calling COMMIT when saving it
> again, or ROLLBACK if closing it without saving.  Simple enough, up until
> the point where I want to implement a "Save As..." menu option, where the
> current state of the application is saved to a new file (new database) with
> the changes, while the changes in the current file are rolled back.
>
> For that, the simplest idea would be to use the online backup family of
> functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the
> database, calling COMMIT on the backup and ROLLBACK on the original.
> Naturally, that doesn't work, as you can't back up a database in the middle
> of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY.

So COMMIT first, then backup.  Just as "Save" == COMMIT, "Save As..."
== COMMIT then backup to new name (or, if you were working with a temp
DB using a temp filename, maybe rename it into place, but the backup
approach is safest.  Presumably you're not racing against another
instance of the same application starting a new transaction on the
same DB (since you seem to keep long-lived transactions anyways), so
this should just work.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Duquette, William H (393K)]

On 4/3/13 2:18 PM, "Nico Williams"  wrote:


>On Wed, Apr 3, 2013 at 4:11 PM, Tiago Rodrigues  wrote:
>> I'm writing a small simulation app and for it I would like to use
>>SQLite3
>> as an application file format, as suggested by the "Appropriate uses for
>> SQLite" page in sqlite.org.  More specifically, the page suggests
>>calling
>> BEGIN TRANSACTION when opening a file and calling COMMIT when saving it
>> again, or ROLLBACK if closing it without saving.  Simple enough, up
>>until
>> the point where I want to implement a "Save As..." menu option, where
>>the
>> current state of the application is saved to a new file (new database)
>>with
>> the changes, while the changes in the current file are rolled back.
>>
>> For that, the simplest idea would be to use the online backup family of
>> functions, calling sqlite3_backup_init() and sqlite3_backup_step() on
>>the
>> database, calling COMMIT on the backup and ROLLBACK on the original.
>> Naturally, that doesn't work, as you can't back up a database in the
>>middle
>> of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY.
>
>So COMMIT first, then backup.  Just as "Save" == COMMIT, "Save As..."
>== COMMIT then backup to new name (or, if you were working with a temp
>DB using a temp filename, maybe rename it into place, but the backup
>approach is safest.  Presumably you're not racing against another
>instance of the same application starting a new transaction on the
>same DB (since you seem to keep long-lived transactions anyways), so
>this should just work.

I've got exactly this use case.  I've found it most useful to keep my
document files and my run-time DB separate.  When my app starts, it opens
a temp DB; and when you open a document file it copies the tables
individually into the temp DB.  On save it does the opposite.  Works quite
well.  

Will


>
>Nico
>--
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Nico Williams]

As a user I prefer continuous saving + infinite undo.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

[Rob Sciuk]

I'm not sure exactly what you're asking here, but if the question is 
whether to use database blobs vs files, then you might be interested in 
this technical report from Microsoft:


arxiv.org/ftp/cs/papers/0701/0701168.pdf


--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.706.1354   Oshawa, Ont.
r...@controlq.com   Canada, L1H 4H3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Simon Slavin]

On 3 Apr 2013, at 10:52pm, Nico Williams  wrote:

> As a user I prefer continuous saving + infinite undo.

Undo is difficult with SQLite.  For instance, to undo an UPDATE command you 
need to know the original values of all the fields updated, which may be 
different in different rows.

One way to do it is to continue to write different files, possibly using the 
backup API as mentioned upthread.

Another way is to keep a log of the SQLite commands issued.  To undo you simply 
start from a saved state and 'replay' all the commands up to the undo point.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Nico Williams]

On Wed, Apr 3, 2013 at 5:08 PM, Simon Slavin  wrote:
> On 3 Apr 2013, at 10:52pm, Nico Williams  wrote:
>> As a user I prefer continuous saving + infinite undo.
>
> Undo is difficult with SQLite.  For instance, to undo an UPDATE command you 
> need to know the original values of all the fields updated, which may be 
> different in different rows.

There are wiki pages on how to do this.  You can use triggers (before
update triggers get old values) to keep a history table(s), or you can
code it in application logic.  There's several pages written about
this, including code.

Another thing you can do is to always have time ranges in the keys,
and the only currently live keys are ones whose start..end ranges
encompass the current datetime.  This is more intrusive, but it's
quite convenient (you can even set rows to be "deleted" at specific
times in the future by setting their end datetimes to something less
than "infinity").

> One way to do it is to continue to write different files, possibly using the 
> backup API as mentioned upthread.

The backup approach doesn't scale so well with file size.  History
doesn't scale well with history, but you can always prune history.

> Another way is to keep a log of the SQLite commands issued.  To undo you 
> simply start from a saved state and 'replay' all the commands up to the undo 
> point.

Sure.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[j . merrill]

The people who are using your software need a lesson about "SQL injection".  No 
one should create SQL statements "on the fly" that include literal character 
strings built from data.  Not only could there be issues if there are special 
characters in the data to be included as a literal string (including the 
possibility of a syntax error that prevents the statement from being executed) 
but evil people could do severe damage -- but this can easily be avoided by 
using parameters.
 
For example, if the value being searched for came from user input (say on a web 
page) users of your software must not do things like this --
 
sql = "select * from mytbl where mycol ='" + input + "'"
 
because, if the input is something like
 
x' ; drop table mytbl; --
 
the table will be dropped!  This cannot happen if parameters are used to pass 
the string.
 
You might be better off providing only a method where the user passes strings 
for the table name ("mytbl" in the example before), the columns to be returned 
(separated by commas, or "*" for all as above), the name of the column to 
compare ("mycol" in the example) and the value to search for.  It would then be 
your code that builds and runs the SQL statement using parameters.
 
J. Merrill
 
-Original Message-
Date: Wed, 3 Apr 2013 22:41:01 +0900

From: Yongil Jang 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] [Question] How can I recognize arguments are
 dynamic binding values in user defined function?
Message-ID:
 
Content-Type: text/plain; charset=EUC-KR

Thank you, Simon and Igor.

I will investigate about your opinion, as you mentioned.

In general, if parameter string contains alphabets only, it doesn't make
any problems.

However, I couldn't check that my function is used correctly for every
applications.
Some developers don't know why does it fails when using special characters
and applications can be packaged with hidden issues.

For this reason, I was looking for some solutions that I can send warning
messages to application developers if they use plain text without binding
arguments.

Best regards,
Yongil jang.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Keith Medcalf]

http://xkcd.com/327/

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of j.merr...@enlyton.com
> Sent: Wednesday, 03 April, 2013 17:17
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] [Question] How can I recognize arguments are dynamic
> binding values in user defined function?
> 
> 
> The people who are using your software need a lesson about "SQL
> injection".  No one should create SQL statements "on the fly" that include
> literal character strings built from data.  Not only could there be issues
> if there are special characters in the data to be included as a literal
> string (including the possibility of a syntax error that prevents the
> statement from being executed) but evil people could do severe damage --
> but this can easily be avoided by using parameters.
> 
> For example, if the value being searched for came from user input (say on
> a web page) users of your software must not do things like this --
> 
> sql = "select * from mytbl where mycol ='" + input + "'"
> 
> because, if the input is something like
> 
> x' ; drop table mytbl; --
> 
> the table will be dropped!  This cannot happen if parameters are used to
> pass the string.
> 
> You might be better off providing only a method where the user passes
> strings for the table name ("mytbl" in the example before), the columns to
> be returned (separated by commas, or "*" for all as above), the name of
> the column to compare ("mycol" in the example) and the value to search
> for.  It would then be your code that builds and runs the SQL statement
> using parameters.
> 
> J. Merrill
> 
> -Original Message-
> Date: Wed, 3 Apr 2013 22:41:01 +0900
> 
> From: Yongil Jang 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] [Question] How can I recognize arguments are
>  dynamic binding values in user defined function?
> Message-ID:
>  
> Content-Type: text/plain; charset=EUC-KR
> 
> Thank you, Simon and Igor.
> 
> I will investigate about your opinion, as you mentioned.
> 
> In general, if parameter string contains alphabets only, it doesn't make
> any problems.
> 
> However, I couldn't check that my function is used correctly for every
> applications.
> Some developers don't know why does it fails when using special characters
> and applications can be packaged with hidden issues.
> 
> For this reason, I was looking for some solutions that I can send warning
> messages to application developers if they use plain text without binding
> arguments.
> 
> Best regards,
> Yongil jang.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Thank you, J. and Keith!


2013/4/4 Keith Medcalf 

>
> http://xkcd.com/327/
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of j.merr...@enlyton.com
> > Sent: Wednesday, 03 April, 2013 17:17
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] [Question] How can I recognize arguments are
> dynamic
> > binding values in user defined function?
> >
> >
> > The people who are using your software need a lesson about "SQL
> > injection".  No one should create SQL statements "on the fly" that
> include
> > literal character strings built from data.  Not only could there be
> issues
> > if there are special characters in the data to be included as a literal
> > string (including the possibility of a syntax error that prevents the
> > statement from being executed) but evil people could do severe damage --
> > but this can easily be avoided by using parameters.
> >
> > For example, if the value being searched for came from user input (say on
> > a web page) users of your software must not do things like this --
> >
> > sql = "select * from mytbl where mycol ='" + input + "'"
> >
> > because, if the input is something like
> >
> > x' ; drop table mytbl; --
> >
> > the table will be dropped!  This cannot happen if parameters are used to
> > pass the string.
> >
> > You might be better off providing only a method where the user passes
> > strings for the table name ("mytbl" in the example before), the columns
> to
> > be returned (separated by commas, or "*" for all as above), the name of
> > the column to compare ("mycol" in the example) and the value to search
> > for.  It would then be your code that builds and runs the SQL statement
> > using parameters.
> >
> > J. Merrill
> >
> > -Original Message-
> > Date: Wed, 3 Apr 2013 22:41:01 +0900
> >
> > From: Yongil Jang 
> > To: General Discussion of SQLite Database 
> > Subject: Re: [sqlite] [Question] How can I recognize arguments are
> >  dynamic binding values in user defined function?
> > Message-ID:
> >  
> > Content-Type: text/plain; charset=EUC-KR
> >
> > Thank you, Simon and Igor.
> >
> > I will investigate about your opinion, as you mentioned.
> >
> > In general, if parameter string contains alphabets only, it doesn't make
> > any problems.
> >
> > However, I couldn't check that my function is used correctly for every
> > applications.
> > Some developers don't know why does it fails when using special
> characters
> > and applications can be packaged with hidden issues.
> >
> > For this reason, I was looking for some solutions that I can send warning
> > messages to application developers if they use plain text without binding
> > arguments.
> >
> > Best regards,
> > Yongil jang.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Stephen Chrzanowski]

Personally, I only use transactions when I'm doing inserts, updates, and
deletes for one or more records.  I never keep a transaction open for
longer than I need to make changes to the database.

What I would suggest is scrap the idea of keeping a transaction open at all
times, but work off of a backup of the original, and make your constant
data updates to the backup.  You can use the SQLite backup API and copy the
original database to memory, or, if tight memory constraints exist, backup
to a temporary file elsewhere on the file system.  Run transactions when
something worth while is to be sent to the database, such as a form on the
UI has been completed.  The effect of doing it in this manor is that when
the user wants to use "SAVE", you use the SQLite API to backup and
overwrite the original file, and still maintain the handle on the work
file.  If the user wants to "SAVE AS", you use the backup API only when a
successful "Save As" prompt occurs, change the string pointer of the most
recent "SAVE" (Which I would bet is probably just a string in the "Open"
dialog).  The business logic of "Save As" can also change at this point as
well as to whether the next "SAVE" is going to overwrite the original file,
or the file you just saved.  SOP is usually to save to the most recent
file.

Perks for this method:
* If something happens and the data integrity becomes compromised while the
software is in use, you're screwed on the work file, not the original.
* If you use a file system to store your work in progress and you have a
power outage, you have the opportunity to continue from wherever you were
from a data standpoint, depending on what state the database was in when
the power outage hit.
* If you want to implement timed backups, all you have to do is set a timer
and call the backup API command against your (lets say) in-memory database
to a new file.  **The caveat of using the backup API is that IF your
beating on the work file constantly, and the database is large, the backup
may not be able to complete.  If a change happens to the work database
while the backup is happening, the backup starts over from square one.  If
you can stop the simulation while the backup is in progress, you can
probably get around that minor limitation.


On Wed, Apr 3, 2013 at 5:11 PM, Tiago Rodrigues  wrote:

> Hello all,
>
> I'm writing a small simulation app and for it I would like to use SQLite3
> as an application file format, as suggested by the "Appropriate uses for
> SQLite" page in sqlite.org.  More specifically, the page suggests calling
> BEGIN TRANSACTION when opening a file and calling COMMIT when saving it
> again, or ROLLBACK if closing it without saving.  Simple enough, up until
> the point where I want to implement a "Save As..." menu option, where the
> current state of the application is saved to a new file (new database) with
> the changes, while the changes in the current file are rolled back.
>
> For that, the simplest idea would be to use the online backup family of
> functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the
> database, calling COMMIT on the backup and ROLLBACK on the original.
> Naturally, that doesn't work, as you can't back up a database in the middle
> of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY.
>
> That being said, has anyone on the list encountered (and hopefully solved)
> this particular problem?  This probably means I have to scrap the idea of
> holding a transaction through the editing of the file, but then should I
> copy the database to a memory-based db?  Any particular tips or caveats?
>
> I appreciate the attention,
>
>   -Tiago
>
> --
> In those days, in those distant days, in those nights, in those remote
> nights, in those years, in those distant years...
>   - Gilgamesh, Enkidu and the Underworld
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need JDBC driver for SQLite

[Vinoth raj]

Hello All,

I was looking for an authoritative source from where I can get the driver
(jar) for SQLite. SQLite web site do not have any mention for Java support.

Can anyone help in getting the JDBC driver for SQLite (from trusted source
only)?

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users