Re: [sqlite] Null character problem

[Kavita Raghunathan]

I'm apologise for the reminder, but I think I have become a victim of
"thread takeover".

Would someone please kindly answer my questions ?

Kavita


On 7/9/10 11:41 AM, "Kavita Raghunathan" 
wrote:

> Thanks. Sounds like I have to use BLOBs which is not what I'm doing
> currently. 
> 
> 1) I wonder about backward compatibility when I start using this BLOB
> method. There are previous databases out there that don't use BLOBs. Can I
> mix and match ? Or does this require the previous database to be deleted on
> an upgrade ?
> 
> 2) When you say " Use statement paramters and bind the data directly" Are
> you referring to the examples in http://sqlite.org/c3ref/bind_blob.html as
> pointed to by Eric Smith?
> 
> Thanks,
> Kavita
> 
> 
> On 7/9/10 11:30 AM, "Jay A. Kreibich"  wrote:
> 
>> On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the
>> wall:
>>> Hello,
>>> I?m storing encrypted passwords in the sqlite database. The encryption
>>> algorithm generates ?null? character, and therefore the password
>>> strings can have nulls in them.
>>> 
>>>  1.  Is this an issue for storing in database ? If strcpy is used
>>>  anywhere, it would be a problem
>> 
>>   They can't be stored as text values without some type of encoding
>>   (like base64), but they can be stored as BLOBs.
>> 
>>>  2.  I?m using sprintf to generate the SQL statement as shown below.
>>>  This causes a problem because sprintf stops printing when it
>>>  encounters ?null?.
>> 
>>   Don't do that.  Use statement parameters and bind the data directly.
>>
>>-j
> 
> ___
> 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] Null character problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 10:54 AM, Eric Smith wrote:
> My question came purely from a mild curiosity.  I was wondering about 
> the behavior of sqlite call sqlite3_bind_text when it is passed a range 
> of BYTES that includes nulls.

bind_text and bind_blob use the same backend function, passing it their
parameters (byte pointer and length) as well as an encoding parameter (0 for
blob, SQLITE_UTF8 or SQLITE_UTF16NATIVE).  If the encoding doesn't match the
database encoding then it is changed.

The important thing is that the bytes are taken exactly as is.  They are not
looked at unless the encoding needs to be changed.  The routine to change
encoding (sqlite3VdbeMemTranslate) does not return an error if the bytes are
not actually valid UTF8/16.

In other words if you give SQLite something claiming it is a string then
SQLite takes you at your word and does not do a round of double checking.
It will return that same "string" when retrieving that value (ie same length
and byte sequence).  This is why you can include embedded nulls.  It is also
why you can feed it bytes in IS8859-1 and things will seem to be okay.  (One
of the Python wrappers allows that!)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3aWoACgkQmOOfHg372QT3vACgtVRoq/l8HZeDLd/QYwdt50NN
qZ0AoM48Qu3ubM3Ld3FfQMjnyxv/WJkA
=i6MM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Virgilio Alexandre Fornazin]

>> My question came purely from a mild curiosity.  I was wondering about 
>> the behavior of sqlite call sqlite3_bind_text when it is passed a range 
>> of BYTES that includes nulls.

See this snipper for documentation:

"The third argument is the value to bind to the parameter.

In those routines that have a fourth argument, its value is the number of
bytes in the parameter. To be clear: the value is the number of bytes in the
value, not the number of characters. If the fourth parameter is negative,
the length of the string is the number of bytes up to the first zero
terminator.

The fifth argument to sqlite3_bind_blob(), sqlite3_bind_text(), and
sqlite3_bind_text16() is a destructor used to dispose of the BLOB or string
after SQLite has finished with it. If the fifth argument is the special
value SQLITE_STATIC, then SQLite assumes that the information is in static,
unmanaged space and does not need to be freed. If the fifth argument has the
value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data
immediately, before the sqlite3_bind_*() routine returns."

http://sqlite.org/c3ref/bind_blob.html

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Smith
Sent: sexta-feira, 9 de julho de 2010 14:54
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Null character problem

Simo Slavin wrote: 

> (according to your earlier post) 

I'm not OP.  I'm Eric.  OP was someone else.  In this context, I don't 
care about blobs or about the right way of doing anything.

> Read the documentation for memset().  

I know quite well how memset works.  I know character!=byte.  These 
matters are irrelevant to my question.  

My question came purely from a mild curiosity.  I was wondering about 
the behavior of sqlite call sqlite3_bind_text when it is passed a range 
of BYTES that includes nulls.

-- 
Eric A. Smith

It's up.  It sorta works.  That's a start.
-- BJ Premore
___
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] Null character problem

[Eric Smith]

Simo Slavin wrote: 

> (according to your earlier post) 

I'm not OP.  I'm Eric.  OP was someone else.  In this context, I don't 
care about blobs or about the right way of doing anything.

> Read the documentation for memset().  

I know quite well how memset works.  I know character!=byte.  These 
matters are irrelevant to my question.  

My question came purely from a mild curiosity.  I was wondering about 
the behavior of sqlite call sqlite3_bind_text when it is passed a range 
of BYTES that includes nulls.

-- 
Eric A. Smith

It's up.  It sorta works.  That's a start.
-- BJ Premore
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[H. Phil Duby]

On Fri, Jul 9, 2010 at 10:48 AM, Eric Smith  wrote:
> Will sqlite3_bind_text work properly if the string contains (internal)
> nulls?  What if I did something like:
>
> char zText[100];
> memset(zText, 0, sizeof(zText));
> sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT);

If you *really* want to work with your password information as a
string / using string functions, convert / encode it to regular text
first.  The trivial case would encode to hex characters, changing your
null characters to '00', which any of the string functions would
happily handle.  That encoding would double the storage space needed
for the password, but string handling would be *safe*.  Other
encodings [that produce standard text characters] would also work.
These would still require more storage than the raw 'binary' data you
have, but most would be less than the double storage needed by the hex
characters.
--
Phil
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Simon Slavin]

On 9 Jul 2010, at 5:48pm, Eric Smith wrote:

> Simon Slavin wrote: 
> 
>> BLOBs can handle any sequences of bytes without problems, 
>> including nulls, ETX, and sequences which be illegal if they were used to 
>> express Unicode characters.  You can put anything you like in a BLOB.  
> 
> I assume, due to the manifest typing semantics of the library, that 
> the declared type of the column will make no difference when I bind a 
> weird datum to a variable (as long as it's not an 'INTEGER PRIMARY KEY' 
> column).

You can use the _bind_blob routine to bind something which will eventually be 
stored in a column with TEXT affinity.  (Sorry, I hate that sentence but I 
can't figure out how to improve it.)  There are no problems doing this.

> Will sqlite3_bind_text work properly if the string contains (internal) 
> nulls?

There are many different meanings for 'string' and I can't answer any question 
about 'properly' until you describe in great detail what you mean by 'string'.  
On the other hand _bind_blob doesn't refer to strings at all.

> What if I did something like: 
> 
> char zText[100];
> memset(zText, 0, sizeof(zText)); 
> sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT);

Read the documentation for memset().  It does not take chars as parameters and 
does not use them internally.

You are using a _text routine with an array of 'char's.  Those lines will work 
perfectly together.  But they has nothing to do with using BLOBs in SQLite.  
For that you would not use _bind_text, you'd use _bind_blob with things that 
probably do not represent characters.  Possibly 'unsigned int' would be a more 
appropriate type than 'char'.

> According to a strict reading of the doc, sqlite will blindly copy
> sizeof(zText) characters (starting from zText[0]) into the column.  
> That is, this will store 100 null bytes into the column.  Is that 
> right?

Text is not bytes, it's characters.  Text involves interpretation.  BLOBs, on 
the other hand, are just sequences of bytes which are treated without any 
attempt to interpret or convert them at all.  Since this is what your data 
actually is (according to your earlier post) you should be declaring your 
column as a BLOB column and using _blob routines to handle them.

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

Re: [sqlite] Null character problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 09:30 AM, Jay A. Kreibich wrote:
>   They can't be stored as text values without some type of encoding
>   (like base64), but they can be stored as BLOBs.

Nulls can be stored in text values.  Behind the scenes SQLite treats strings
and blobs almost identically and you'll notice most functions operate on
both.  The major difference is that strings can be transformed into UTF-8 or
UTF-16 encoding.  Other than that they are both a bucket of bytes.

As an example look at the implementation of sqlite3_bind_{blob,text} or
trimFunc and replaceFunc.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3WrsACgkQmOOfHg372QTzbACfVVfmAsmSBdvv6WwPtd00DzHt
/z4AoMORO0XCFsc3g3csCL6QA5meBzmM
=g/YJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 09:24 AM, Kavita Raghunathan wrote:
>  1.  Is this an issue for storing in database ? If strcpy is used anywhere, 
> it would be a problem

SQLite quite happily stores/retrieves null bytes in strings.  It is part of
my test suite.  If you use the SQLite API correctly then it is fine.  (ie
*you* must not use strcpy etc).

Some of the SQL level functions will however stop at the first null such as
string concatenation or replace.

Note however that strings are to store Unicode strings, encoded as UTF-8 or
UTF-16 depending on the API flavour you use.  Do not save binary data into a
string.

>  2.  I’m using sprintf to generate the SQL statement as shown below. 

As others have pointed out this is a really bad idea as it allows for SQL
injection bugs and attacks.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3WAcACgkQmOOfHg372QSNigCgpxjYX4Rktm7qePeZB/bKrZHs
tHkAoOIvWAhNMFjI5P8F5sy7ZuXfZkD2
=T/px
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Eric Smith]

Simon Slavin wrote: 

> BLOBs can handle any sequences of bytes without problems, 
> including nulls, ETX, and sequences which be illegal if they were used to 
> express Unicode characters.  You can put anything you like in a BLOB.  

I assume, due to the manifest typing semantics of the library, that 
the declared type of the column will make no difference when I bind a 
weird datum to a variable (as long as it's not an 'INTEGER PRIMARY KEY' 
column).  

Will sqlite3_bind_text work properly if the string contains (internal) 
nulls?  What if I did something like: 

char zText[100];
memset(zText, 0, sizeof(zText)); 
sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT); 

According to a strict reading of the doc, sqlite will blindly copy
sizeof(zText) characters (starting from zText[0]) into the column.  
That is, this will store 100 null bytes into the column.  Is that 
right?

Eric 

-- 
Eric A. Smith

When you come to a fork in the road, take it.
-- Yogi Berra
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Simon Slavin]

On 9 Jul 2010, at 5:29pm, Eric Smith wrote:

> I have no specific knowledge on whether sqlite handles null characters
> within the variables' values--but if I were a bettin man, I'd bet that
> it handles them quite cleanly.

You win.  BLOBs can handle any sequences of bytes without problems, including 
nulls, ETX, and sequences which be illegal if they were used to express Unicode 
characters.  You can put anything you like in a BLOB.

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

Re: [sqlite] Null character problem

[Jay A. Kreibich]

On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the 
wall:
> Hello,
> I?m storing encrypted passwords in the sqlite database. The encryption
> algorithm generates ?null? character, and therefore the password
> strings can have nulls in them.
> 
>  1.  Is this an issue for storing in database ? If strcpy is used
>  anywhere, it would be a problem

  They can't be stored as text values without some type of encoding
  (like base64), but they can be stored as BLOBs.

>  2.  I?m using sprintf to generate the SQL statement as shown below.
>  This causes a problem because sprintf stops printing when it
>  encounters ?null?.

  Don't do that.  Use statement parameters and bind the data directly.
   
   -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] Null character problem

[Eric Smith]

Kavita Raghunathan wrote: 

> sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType, 
> AttrValue, ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d, 
> %d);", tbl_name, db[i]->attr_name, db[i]->attr_num, db[i]->attr_type, 
> db[i]->attr_value, db[i]->attr_src, entity_id); 

Don't do that.  What if attr_name contains a ' character (or, as you
say, some other weird character)?

Instead, prepare a statement with sqlite variables, and bind values to
those variables using the sqlite3_bind* family of interfaces:
http://sqlite.org/c3ref/bind_blob.html

I have no specific knowledge on whether sqlite handles null characters
within the variables' values--but if I were a bettin man, I'd bet that
it handles them quite cleanly.

-- 
Eric A. Smith

I think there's a world market for about five computers.
-- attr. Thomas J. Watson (Chairman of the Board, IBM), 1943
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users