Re: [sqlite] Null character problem
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
-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
>> 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
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
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
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
-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
-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
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
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
Re: [sqlite] Null character problem
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
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
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
[sqlite] Null character problem
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 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”. Please advice. 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); Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users