Re: [sqlite] Bug report: sqlite3_column_name vs. LIKE clause in prepared statements

[Richard Hipp]

On Mon, Mar 28, 2011 at 7:44 PM, Klaus Keppler  wrote:

> Hi,
>
> I'd like to submit the following bug report. However, before suspecting
> the problem in SQLite, we've debugged and traced our own code for two
> days, and at last got it reproducible within SQLite.
>
> DESCRIPTION:
> It's an absolute strange behaviour: if you do queries with the LIKE
> operator using the new sqlite_prepare_v2() interface *and* using host
> parameters, the column names returned by sqlite3_column_name() get
> invalid after the first call to sqlite3_step()
>
> According to the documentation, the string pointer returned by
> sqlite3_column_name() "is valid until either the prepared statement is
> destroyed by sqlite3_finalize() or until the next call to
> sqlite3_column_name() [...] on the same column"
> (see http://www.sqlite.org/capi3ref.html#sqlite3_column_name )
>

Thanks for your hard work in tracking this down.

You are correct, this is a documentation bug.  What is happening is that
sometimes SQLite will automatically re-prepare a statement on the first call
to sqlite3_step().  When that happens, it really creates a new prepared
statement, finalizes the old one, then copies the new one over top of the
old one.  But that finalization of the old prepared statement is the same as
calling sqlite3_finalize() in that it cases the old column names to be
invalidated.

I have updated the documentation here:
http://www.sqlite.org/src/fdiff?v1=2ab8766c32afed16=e047f69a61d604d4



>
> We've prepared a short sample code to illustrate the problem.
> Code was tested with SQLite 3.7.5 using
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT; platform is AMD64 (with Debian 5)
>
>
> When running this test either without host parameters, or with the "old"
> sqlite_prepare() interface, everything works fine.
>
> We don't have the resources to investigate deeper into the SQLite source
> code, but I'm sure that the sample code makes the problem easily visible.
>
> The issue is propably not critical, but within our application we
> cache(d) the column names after preparing the statements once, so this
> behaviour at least caused some serious trouble when trying to access the
> row data by "our" column names.
> The most straightforward "bug fix" would be an documentation update,
> stating that the returned string pointers maybe are invalid after
> sqlite3_step(). ;-)
>
> For any questions don't hezitate to contact me.
>
> Best regards
>
>Klaus Keppler
>
>
> Sample code
> (please adjust to any test table; schema doesn't matter, we tried
> many different variants)
> ---cut---
> /* Test-Code; Public Domain */
>
> #include 
> #include 
> #include 
>
> #define SQLITE_API
> #define SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> #include "sqlite3.h"
>
> int main(void) {
>sqlite3 *dbh = NULL;
>sqlite3_stmt *stmt = NULL;
>int done;
>const char *search = "%u%";
>const char *sql = "SELECT LOG_MESSAGE FROM LOG WHERE LOG_MESSAGE
> LIKE :1";
>const char *name = NULL;
>
>if (sqlite3_open("sqlite.db", ) != SQLITE_OK) {
>printf("sqlite3_open() failed\n");
>exit(-1);
>}
>
>if (sqlite3_prepare_v2(
>dbh,
>sql,
>strlen(sql)+1,
>,
>NULL) != SQLITE_OK) {
>printf("sqlite3_prepare() failed\n");
>exit(-1);
>}
>
>/* get column name */
>name = sqlite3_column_name(stmt, 0);
>printf("Column name (1): '%s'\n", name == NULL ? "(NULL)" : name);
>
>/* bind parameter */
>if (sqlite3_bind_text(
>stmt,
>1,
>search,
>search == NULL ? 0 : strlen(search),
>SQLITE_STATIC) != SQLITE_OK) {
>printf("sqlite3_bind() failed\n");
>exit(-1);
>}
>
>printf("Column name (2): '%s'\n", name == NULL ? "(NULL)" : name);
>
>done = 0;
>while (!done) {
>int r = sqlite3_step(stmt);
>printf("Column name (3): '%s'\n", name == NULL ? "(NULL)" :
> name);
>const unsigned char *ch;
>switch (r) {
>case SQLITE_ROW:
>/* we have a result row */
>ch = sqlite3_column_text(stmt, 0);
>printf("  result: '%s'\n", ch == NULL ?
> "(NULL)" : (char*)ch);
>break;
>case SQLITE_DONE:
>/* we're done */
>printf("  (done)\n");
>done=1;
>break;
>default:
>printf("  RESULT: %i\n", r);
>exit(-1);
>  

[sqlite] Bug report: sqlite3_column_name vs. LIKE clause in prepared statements

[Klaus Keppler]

Hi,

I'd like to submit the following bug report. However, before suspecting 
the problem in SQLite, we've debugged and traced our own code for two 
days, and at last got it reproducible within SQLite.

DESCRIPTION:
It's an absolute strange behaviour: if you do queries with the LIKE 
operator using the new sqlite_prepare_v2() interface *and* using host 
parameters, the column names returned by sqlite3_column_name() get 
invalid after the first call to sqlite3_step()

According to the documentation, the string pointer returned by 
sqlite3_column_name() "is valid until either the prepared statement is 
destroyed by sqlite3_finalize() or until the next call to 
sqlite3_column_name() [...] on the same column"
(see http://www.sqlite.org/capi3ref.html#sqlite3_column_name )

We've prepared a short sample code to illustrate the problem.
Code was tested with SQLite 3.7.5 using 
SQLITE_ENABLE_UPDATE_DELETE_LIMIT; platform is AMD64 (with Debian 5)


When running this test either without host parameters, or with the "old" 
sqlite_prepare() interface, everything works fine.

We don't have the resources to investigate deeper into the SQLite source 
code, but I'm sure that the sample code makes the problem easily visible.

The issue is propably not critical, but within our application we 
cache(d) the column names after preparing the statements once, so this 
behaviour at least caused some serious trouble when trying to access the 
row data by "our" column names.
The most straightforward "bug fix" would be an documentation update, 
stating that the returned string pointers maybe are invalid after 
sqlite3_step(). ;-)

For any questions don't hezitate to contact me.

Best regards

Klaus Keppler


Sample code
(please adjust to any test table; schema doesn't matter, we tried
many different variants)
---cut---
/* Test-Code; Public Domain */

#include 
#include 
#include 

#define SQLITE_API
#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT
#include "sqlite3.h"

int main(void) {
sqlite3 *dbh = NULL;
sqlite3_stmt *stmt = NULL;
int done;
const char *search = "%u%";
const char *sql = "SELECT LOG_MESSAGE FROM LOG WHERE LOG_MESSAGE LIKE 
:1";
const char *name = NULL;

if (sqlite3_open("sqlite.db", ) != SQLITE_OK) {
printf("sqlite3_open() failed\n");
exit(-1);
}

if (sqlite3_prepare_v2(
dbh,
sql,
strlen(sql)+1,
,
NULL) != SQLITE_OK) {
printf("sqlite3_prepare() failed\n");
exit(-1);
}

/* get column name */
name = sqlite3_column_name(stmt, 0);
printf("Column name (1): '%s'\n", name == NULL ? "(NULL)" : name);

/* bind parameter */
if (sqlite3_bind_text(
stmt,
1,
search,
search == NULL ? 0 : strlen(search),
SQLITE_STATIC) != SQLITE_OK) {
printf("sqlite3_bind() failed\n");
exit(-1);
}

printf("Column name (2): '%s'\n", name == NULL ? "(NULL)" : name);

done = 0;
while (!done) {
int r = sqlite3_step(stmt);
printf("Column name (3): '%s'\n", name == NULL ? "(NULL)" : 
name);
const unsigned char *ch;
switch (r) {
case SQLITE_ROW:
/* we have a result row */
ch = sqlite3_column_text(stmt, 0);
printf("  result: '%s'\n", ch == NULL ? 
"(NULL)" : (char*)ch);
break;
case SQLITE_DONE:
/* we're done */
printf("  (done)\n");
done=1;
break;
default:
printf("  RESULT: %i\n", r);
exit(-1);
}
}

/* free statement */
sqlite3_finalize(stmt);
stmt = NULL;

/* close connection */
sqlite3_close(dbh);
dbh = NULL;

printf("Done.\n");
return(0);
}
---/cut---

Output: (the SELECT returned two rows)
---cut---
Column name (1): 'LOG_MESSAGE'
Column name (2): 'LOG_MESSAGE'
Column name (3): 'Ø$Z'
   result: '[...]'
Column name (3): 'Ø$Z'
   result: '[...]'
Column name (3): 'Ø$Z'
   (done)
Done.
---/cut---


-- 
__
Keppler IT GmbH - Die Hostingexperten.

Dipl.-Inf. Klaus KepplerTel. (09131) 691-480
Geschäftsführer Fax: (09131) 691-489

Am Weichselgarten 7 UStID.-Nr. DE259788698
91058 Erlangen  Amtsgericht Fürth, HRB 11477
www.keppler-it.de