Re: [sqlite] disk image malformed

[Robert Myers]

One thing I haven't seen anyone ask yet - are you putting this on a
network drive?
On 12/6/2012 10:52 AM, Durga D wrote:
> Hi,
>
>   Is it possible to corrupt a single table among 10 tables in a database?
>
>   Is it possible to corrupt some records among millions of records in a
> table?
>
> Best Regards,
> ___
> 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] SQLITE in a Win7 Service Application

[Robert Myers]

If you're using impersonation, you'll run into problems doing any 
updates during the impersonation when sqlite tries to create any 
temporary files or reopen any it has closed.


You can use Process Monitor from Sysinternals (now Microsoft) to see 
exactly what the failure is with what file.


On 8/15/2012 10:19 AM, markus ruettimann wrote:

Hi,
We are facing problems when the sqlite database () is used within an
application that runs as a Win7 service under the local system account.
The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC
driver.

How ever we can create a new database, reading and writing to it withount
any problem. The only thing that is not working is the ATTACH DATABASE SQL
command. We constantly receive a CAN_NOT_OPEN error code.

When we start the same application under a local user everything works
fine. I have to add the both databases that are to be attached are in the
same folder and have been created by the Application itself. There are no
access restrictions on the database folders or files.

Does anyone know about this and / or has a solution?

Kind regards
Markus
___
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] C++ - Installer

[Robert Myers]

On 8/2/2012 8:40 AM, Arbol One wrote:

I was wondering if there is an 'Installer' for Windows 7 that you would
recommend. I want my user to have all the necessary SQLite3 files needed for
my application as well as all the gtkmm files.

  


You can create installers with WiX (http://wix.sourceforge.net). If you 
want a more GUI oriented tool, there's always Installshield.


Unless your app is .Net, you should be building sqlite right into the 
binary and there's nothing you need to distribute there, unless you want 
a skeleton db.


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

Re: [sqlite] how to process password

[Robert Myers]

Encryption is very hard to do right, so in general, let someone else do it.

We use SEE (http://www.hwaci.com/sw/sqlite/see.html) with the key
generated from a password by PBKDF2 and a high number of iterations.
Previously, columns were encrypted with OS functions that made it
impossible to move the database from machine to machine, as well as
really bloating up the data in those columns.

Don't forget to pull the password from secure functions, don't take it
from the command line or getline.

On 7/27/2012 9:50 AM, yanhong.ye wrote:
> I need insert any my bank-card information to sqlite db, so when I insert the 
> car number like '3312' and car password like '7711', but I wanna nobody can 
> see the password, so I create a function encode('7711','key') and 
> decode(select from stable where carnum=3312, 'key') to see the password, the 
> key is like 'plkey777' to encode or decode the password , but I don't know 
> how to realize that. and I have a function to encode and decode :  
> procode(aa,bb,flag);
> char *aa="plkey777";
> char *bb="7711";
> procode(aa,bb,1);   //here is to encoding the char ,bb result is bb
>
> insert into stable select "3312",bb;
>
>
> procode(aa,bb,2);//here is to decoding the char ,bb result is bb
> select carbumber,procode(aa,bb,2) from stable 
>
> I don't know how to do this. the code upside isPseudo code。
> ___
>

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

Re: [sqlite] C++ - All the data in ONE row

[Robert Myers]

On 7/25/2012 10:02 AM, Arbol One wrote:

OK, continuing with the writing to the database, here is the driver method
for the class rapper for SQLite3.

The problem I have is that I have no control as to the exact location where
I want the data to be stored, look the table below to have a better idea of
what I mean.

  


My question is, is there a function that will allow me to directly plug the
data at that specific index I want?

Ultimately, what I want to do is to store all the data in ONE row. How is
this handle in SQLite3?
You're using bind wrong. Don't use the same position each time, do all 
the binds then do the step.


As before, you have a resource leak in this. You don't have a way to 
finalize the statement on error.


Rob

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

Re: [sqlite] C++ - Creating Table

[Robert Myers]

On 7/23/2012 10:51 PM, Keith Medcalf wrote:

And of course, finalize after close is wrong.  You finalize the statement, then 
close the db, then bail.
Which goes back to the point I was making, don't manage resources 
yourself. Let the compiler do it. The close should've been only in the 
database object destructor (or, for more flexibility, in a close method 
that happens to be called by the destructor).


Ideally, you should never be using any of the sqlite handle types 
anywhere in your code, except for the objects that manage them. This is 
probably not obtainable in practice without a lot of work and/or 
performance impacting code, or using someone else's library. No matter 
what though, all the creation / destruction calls should be through the 
object, since that's where it really matters.



---
()  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 Robert Myers
Sent: Monday, 23 July, 2012 21:44
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] C++ - Creating Table

On 7/23/2012 7:43 PM, Arbol One wrote:

Thank you Michael for your prompt response.
I have been able to duplicate the error message. I think this could be a

bug

in SQLite3.
void jme::mySQLite3::createTable(const std::string& s) throw
(std::exception) {
  rc = sqlite3_prepare_v2( db, s.c_str(), -1, , NULL);
  if(rc != SQLITE_OK) {

rc is a bool, not an int, you have a type coercion error here.


  sqlite3_close(db);
  sqlite3_finalize(stmt);
  std::string error("Error prepare_v2: ");
  error += sqlite3_errmsg(db);
  std::cout << "Error: " << rc << " " << error << std::endl;
  }
  rc = sqlite3_step(stmt);

There's a use after free error here  (you don't return / throw after
finalizing)

  std::cout << "Error: " << rc << std::endl;

  if(rc != SQLITE_DONE) {

And another type coercion error here. rc will never be equal to SQLITE_DONE.


  sqlite3_close(db);
  sqlite3_finalize(stmt);

And another use after free here, since the step will fail, you'll close
it again. Of course, that assumes the step isn't going to crash in the
first place.

  std::string error("error sqlite3_step: ");
  error += sqlite3_errmsg(db);
  std::cout << error << std::endl;
  }
  sqlite3_finalize(stmt);

And another use after free here, same problem with the error handling.

You're committing one of the cardinal sins of C++. Let the compiler do
all the work, don't do it yourself, you'll (generic you) screw it up.
Wrap the DB and query in objects that handle the clean up for you.

Compile everything (except sqlite3.c itself) at the highest possible
warning level treating warnings as errors. That would've caught the type
error. It's one of the first things I set up with new projects.

Rob

___
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] C++ - Creating Table

[Robert Myers]

On 7/23/2012 7:43 PM, Arbol One wrote:

Thank you Michael for your prompt response.
I have been able to duplicate the error message. I think this could be a bug
in SQLite3.
void jme::mySQLite3::createTable(const std::string& s) throw
(std::exception) {
 rc = sqlite3_prepare_v2( db, s.c_str(), -1, , NULL);
 if(rc != SQLITE_OK) {

rc is a bool, not an int, you have a type coercion error here.


 sqlite3_close(db);
 sqlite3_finalize(stmt);
 std::string error("Error prepare_v2: ");
 error += sqlite3_errmsg(db);
 std::cout << "Error: " << rc << " " << error << std::endl;
 }
 rc = sqlite3_step(stmt);
There's a use after free error here  (you don't return / throw after 
finalizing)

 std::cout << "Error: " << rc << std::endl;

 if(rc != SQLITE_DONE) {

And another type coercion error here. rc will never be equal to SQLITE_DONE.


 sqlite3_close(db);
 sqlite3_finalize(stmt);
And another use after free here, since the step will fail, you'll close 
it again. Of course, that assumes the step isn't going to crash in the 
first place.

 std::string error("error sqlite3_step: ");
 error += sqlite3_errmsg(db);
 std::cout << error << std::endl;
 }
 sqlite3_finalize(stmt);


And another use after free here, same problem with the error handling.

You're committing one of the cardinal sins of C++. Let the compiler do 
all the work, don't do it yourself, you'll (generic you) screw it up. 
Wrap the DB and query in objects that handle the clean up for you.


Compile everything (except sqlite3.c itself) at the highest possible 
warning level treating warnings as errors. That would've caught the type 
error. It's one of the first things I set up with new projects.


Rob

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

Re: [sqlite] Bug in sqlite3_step

[Robert Myers]

On 7/3/2012 3:26 PM, Richard Hipp wrote:


Igor is correct.

Actually, you can bind on a DDL statement, but bindings are only valid for
the lifetime of the statement itself, not for the whole lifetime of the
object created by the CREATE statement.  So doing such bindings are
pointless.  And you can't really bind to a DROP statement, because there
are no expressions in a DROP statement to bind to.  But these are all
details.  Igor's explanation is simple and to the point.



DROP TABLE ? would've been useful for me. As it is, I just generate the 
statement on the fly. That's something that gives me the willies, even 
though I'm fairly certain I have full control of the statement since I'm 
in SEE.


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

Re: [sqlite] size_t printf standard

[Robert Myers]

On 6/29/2012 1:56 PM, Kyle McKay wrote:

On June 28, 2012 09:46:06 PDT, Stephan Beal wrote:

- There are no standard printf()/scanf() specifiers for it, which means
those funcs cannot be used with size_t or ifdefs or casts are needed to
handle them portably.


Perhaps you should actually check the standard before making such a 
claim:


http://pubs.opengroup.org/onlinepubs/009695399/functions/printf.html


There are standardized ones, but not standard ones; a subtle, but 
important, distinction.


Compare your link to http://msdn.microsoft.com/en-us/library/tcxf1dw6.aspx

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

Re: [sqlite] SQLite4 (don't scream)

[Robert Myers]

Aghhh (just kidding)

One request for a change - make bind and column start with the same 
index. I always have to look up which one is zero based and which one is 
one based.


Rob

On 6/28/2012 10:57 AM, Simon Slavin wrote:

First, the important bit:

"SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going 
away."

Now the URL:



Just thought some people might enjoy reading and thinking about it.

Simon.
___
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] C++ - ISERT from a data object

[Robert Myers]

On 6/25/2012 5:58 PM, Simon Slavin wrote:

On 25 Jun 2012, at 11:36pm, Arbol One  wrote:


In my GUI application the user enters a information that will go in a SQLite
database table, so statements like:

string dbdata = "INSERT INTO friend (name, address, age) VALUES ('Caramba',
'490 New Bridge', '49')";

are not very useful in a real life C++ GUI  application.

That thing you quoted above ... the thing between the double quotes ... is a 
string.  You can make up the string yourself by concatenating several strings 
together.


I may be a bit oversensitive here, but that seems like an incredibly bad 
thing to suggest, even in jest. People will do it and yet more SQL 
Injection attacks will appear.


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

Re: [sqlite] error compilation with Sqlite in C program

[Robert Myers]

On 4/12/2012 11:07 AM, Sako Youssouf wrote:

Here my step and the result.

# gcc -c sqlite3.c
# ar -rvs libsqlite3.a sqlite3.o
ar: creating libsqlite3.a
a - sqlite3.o

# gcc -L. -lsqlite -L/usr/lib/ -ldl -lpthread -o compil compil.c


Here's your problem. You want -lsqlite3


/tmp/ccfdSnPR.o: In function `main':
compil.c:(.text+0xc3): undefined reference to `sqlite3_open'
compil.c:(.text+0xda): undefined reference to `sqlite3_errmsg'
compil.c:(.text+0x101): undefined reference to `sqlite3_close'
compil.c:(.text+0x138): undefined reference to `sqlite3_exec'
compil.c:(.text+0x16d): undefined reference to `sqlite3_free'
compil.c:(.text+0x179): undefined reference to `sqlite3_close'
collect2: ld a retourné 1 code d'état d'exécution

compil.c :

01  #include
02  #include
03
04  static int callback(void *NotUsed, int argc, char **argv, char **azColName){
05int i;
06for(i=0; i

Re: [sqlite] How to : encrypt the data of database table.

[Robert Myers]

I added some functions to do that. I have ENCCOL, DECCOL, SIGCOL
(signature creation), CHECKSIG

Inserts and selects look something like this:
INSERT INTO TABLE x(col1) VALUES(ENCCOL(1))
SELECT DECCOL(col1) FROM x;

Nothing I want in an index can be encrypted, which is where SIGCOL comes
in, I can sign the columns and make sure that the signature is valid.
The code is far from portable, it's Windows specific, using the keys
associated with the account and APIs like CryptProtectData so the
databases themselves can't be transferred between machines.

There is also a (non-free) encryption version which we may be moving to
in the future. http://www.hwaci.com/sw/sqlite/see.html

On 9/14/2011 12:53 PM, Ashokkumar Gupta wrote:
> Hi All,
>
> Does sqlite has encryption feature or not. If yes, how to do it and if No,
> then is there any work around by which we can have that feature ??
>
> Regards,
> Ashokkumar
> ___
> 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] SQLite3.dll for Win 64

[Robert Myers]

Check your compiler documentation. You should be able to link C .obj
files and call C functions without too much hassle. The biggest issue
will be getting all the functions declared in a Pascal readable format.

On 7/25/2011 3:10 PM, Everton Vieira wrote:
> The software that uses this dll is made in pascal so i can't include c files
> in the project.
>
> 2011/7/25 Teg 
>
>> Hello Everton,
>>
>> EV> Don't have any easy way to make this dll? There's a lot of 64bits
>> systems
>> EV> out there that will need this dll.
>>
>> I just compile it into my program and don't bother with DLL's. I find
>> that DLL's in general make my software less reliable so, I avoid them.
>>
>> Static linking all the way
>>
>> C
>>
>>
>>
>>
>>
>> ___
>> 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] How to search column ascii containing chr(0)

[Robert Myers]

On 6/5/2011 8:47 AM, Igor Tandetnik wrote:
> iip  wrote:
>> As subject, I want to know how search column that contain ascii chr(0)
> select * from MyTable where hex(MyField) like '%00%';

That query doesn't work. If the field contains 0\n, that would match (300A)

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

Re: [sqlite] TRANSACTIONs

[Robert Myers]

On 6/1/2011 1:47 PM, Pavel Ivanov wrote:
>> Actually, you do know what SQLite does without knowing the internals. It
>> claims to be serializable and ACID
>> (http://www.sqlite.org/transactional.html), therefore it's fine.
> "Serializable" there means that once transaction is started statements
> won't see any data committed in other transactions. But it doesn't
> impose any restrictions on how statements should behave within the
> same transaction. And in SQLite it's pretty unpredictable and so even
> SQLite's documentation prohibits updating some table while select
> statement on it is active (I can find a link on sqlite.org for you
> when I have some spare time).
What about the I of ACID? The select should have an implicit transaction
around it.

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

Re: [sqlite] TRANSACTIONs

[Robert Myers]

On 6/1/2011 1:23 PM, Simon Slavin wrote:
> On 1 Jun 2011, at 7:12pm, Jan Hudec wrote:
>
>> On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote:
  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not
 update the same row in a table more than once?
>>> You can update it as many times as you need.
>>>
  What are the exact limitations on what I can do during a Transaction?
>>> Do not update a table if there is some select statement currently
>> Actually insert, update and delete are OK. Drop and alter table are
>> a problem.
> Pavel is right.  He left out some details to make things simple.
>
> Suppose you do a SELECT ... WHERE ... that returns ten rows.  You retrieve 
> three rows, then make a change that would mean you should have retrieved 
> eleven rows, not ten.  You can't predict what SQLite will do without knowing 
> the internal workings of SQLite, right ?  So don't do that.  The same is true 
> even if the only thing you change is values to be returned.  Does SQLite copy 
> the all values when you execute the SELECT, or row-by-row as you step through 
> the results ?  Again, you don't know unless you know the internal workings of 
> SQLite.  So don't do that.
>
> Simon.
> ___

Actually, you do know what SQLite does without knowing the internals. It
claims to be serializable and ACID
(http://www.sqlite.org/transactional.html), therefore it's fine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite and impersonation on Windows (Bug / feature request)

[Robert Myers]

When running in Windows under SYSTEM context, such as in a service,
there are issues when using impersonation and trying to update a sqlite
database.

How to reproduce:
 1. In SYSTEM context, create a directory that only SYSTEM as access to.
 2. Open/ create a database in this directory. All subsequent steps are
assumed to use the connection object opened here.
 3. Impersonate a lower level user, such as the person on console
 4. Insert a record into a table.

Expected Result: Record is inserted
Actual Result: SQLITE_CANTOPEN is returned

Root Cause: The insert attempts to create or open the journals and
write-ahead logs. Since it's now running in user context, the directory
is inaccessible. Any attempt to open or create the file fails with
ERROR_ACCESS_DENIED.

Workaround: Stop impersonation before doing any database operations.

Suggested Fix: Add a PRAGMA or sqlite3_db_config / sqlite3_config option
that keeps the various files associated with a connection open rather
than closing them when done.

Rob

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