Intercepting execution of sqlite3 command to see final query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Intercepting execution of sqlite3 command to see final query

Thomas Nyberg
Hello,

The premise of this is the following: I'm using sqlite3 in a django
application. I'm having it log the SQL that it issues (once you get
passed its magical ORM setup), but that command does not work. I.e. I
have something like the following (the python is not important, but does
set the stage a little):

     >>> regex = 'NET | INCOME'
     >>> Table.objects.filter(name__iregex=regex).

When I have it print out the SQL sent, I get the following (this is a
simplified, but equivalent version):

     SELECT *
     FROM "table"
     WHERE "table"."name"
     REGEXP '(?i)' || 'NET | INCOME'
     LIMIT 21

However if I execute that directly against the sqlite database i get the
following error:

     sqlite3.OperationalError: no such function: REGEXP

I thought that maybe this magic was handled in python's sqlite3 module,
but if i do it there i get the same error.

Finally I realized it might just be easier to intercept the sqlite3 call
at the dynamic library level. To do this, I wrote the following code:

test.c
---------------------------------------
#define _GNU_SOURCE

#include <stdio.h>
#include <dlfcn.h>
#include <sqlite3.h>

SQLITE_API int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
                                sqlite3_stmt **ppStmt, const char
**pzTail) {
     int (*original_sqlite3_prepare)(sqlite3*, const char *, int,
                                     sqlite3_stmt**, const char **);
     original_sqlite3_prepare = dlsym(RTLD_NEXT, "sqlite3_prepare");
     printf("\n\n***ORIGINAL SQL QUERY***\n\n\n");
     printf("%s", zSql);
     printf("\n\n***END ORIGINAL SQL QUERY***\n\n\n");
     fflush(stdout);
     return (*original_sqlite3_prepare)(db, zSql, nByte, ppStmt, pzTail);
}
---------------------------------------

I compiled it as follows:

        $ gcc -Wall -fPIC -shared -o mysqlite3_prepare.so test.c -ldl -lsqlite3

When I preload this before running my app, it prints out the following
(slightly cleaned up to make easier to read by removing a couple
irrelevant fields):

SELECT "table"."id", "table"."name", FROM "table" WHERE "table"."name"
REGEXP '(?i)' || ?

I'm not sure what to do with this. It's even missing the final part. So
obviously I'm looking at this in the wrong place. So I guess I have a
couple questions:

1) How can I sort out what the final query actually is? Possibly I
should be intercepting other calls or functions?
2) Is this obvious the readers here (thus making my other investigations
unnecessary) what's going on?

Thanks for any help!

Cheers,
Thomas

P.S. Appologies if this is not appropriate here? Maybe it should be
posted to sqlite-dev?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Intercepting execution of sqlite3 command to see final query

David Raymond
In the page for expressions (http://www.sqlite.org/lang_expr.html) check out the Parameters section and the REGEXP operator section:

"The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)"."

So whatever you're normally running has probably implemented its own regexp, which would need to be loaded to test it yourself.

As to the other part, you <are> seeing the SQL. The question marks are for bound parameters, the question mark means "get the value from the bound variable". The statement text stays the same the whole time, only the bindings get updated. So if you're looking to log everything that it's doing you'll have to capture it binding those values as well.

Hopefully I'm answering that right.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Nyberg
Sent: Monday, February 13, 2017 3:12 PM
To: [hidden email]
Subject: [sqlite] Intercepting execution of sqlite3 command to see final query

Hello,

The premise of this is the following: I'm using sqlite3 in a django
application. I'm having it log the SQL that it issues (once you get
passed its magical ORM setup), but that command does not work. I.e. I
have something like the following (the python is not important, but does
set the stage a little):

     >>> regex = 'NET | INCOME'
     >>> Table.objects.filter(name__iregex=regex).

When I have it print out the SQL sent, I get the following (this is a
simplified, but equivalent version):

     SELECT *
     FROM "table"
     WHERE "table"."name"
     REGEXP '(?i)' || 'NET | INCOME'
     LIMIT 21

However if I execute that directly against the sqlite database i get the
following error:

     sqlite3.OperationalError: no such function: REGEXP

I thought that maybe this magic was handled in python's sqlite3 module,
but if i do it there i get the same error.

Finally I realized it might just be easier to intercept the sqlite3 call
at the dynamic library level. To do this, I wrote the following code:

test.c
---------------------------------------
#define _GNU_SOURCE

#include <stdio.h>
#include <dlfcn.h>
#include <sqlite3.h>

SQLITE_API int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
                                sqlite3_stmt **ppStmt, const char
**pzTail) {
     int (*original_sqlite3_prepare)(sqlite3*, const char *, int,
                                     sqlite3_stmt**, const char **);
     original_sqlite3_prepare = dlsym(RTLD_NEXT, "sqlite3_prepare");
     printf("\n\n***ORIGINAL SQL QUERY***\n\n\n");
     printf("%s", zSql);
     printf("\n\n***END ORIGINAL SQL QUERY***\n\n\n");
     fflush(stdout);
     return (*original_sqlite3_prepare)(db, zSql, nByte, ppStmt, pzTail);
}
---------------------------------------

I compiled it as follows:

        $ gcc -Wall -fPIC -shared -o mysqlite3_prepare.so test.c -ldl -lsqlite3

When I preload this before running my app, it prints out the following
(slightly cleaned up to make easier to read by removing a couple
irrelevant fields):

SELECT "table"."id", "table"."name", FROM "table" WHERE "table"."name"
REGEXP '(?i)' || ?

I'm not sure what to do with this. It's even missing the final part. So
obviously I'm looking at this in the wrong place. So I guess I have a
couple questions:

1) How can I sort out what the final query actually is? Possibly I
should be intercepting other calls or functions?
2) Is this obvious the readers here (thus making my other investigations
unnecessary) what's going on?

Thanks for any help!

Cheers,
Thomas

P.S. Appologies if this is not appropriate here? Maybe it should be
posted to sqlite-dev?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Intercepting execution of sqlite3 command to see final query

Thomas Nyberg
On 02/13/2017 04:14 PM, David Raymond wrote:

> In the page for expressions (http://www.sqlite.org/lang_expr.html) check out the Parameters section and the REGEXP operator section:
>
> "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)"."
>
> So whatever you're normally running has probably implemented its own regexp, which would need to be loaded to test it yourself.
>
> As to the other part, you <are> seeing the SQL. The question marks are for bound parameters, the question mark means "get the value from the bound variable". The statement text stays the same the whole time, only the bindings get updated. So if you're looking to log everything that it's doing you'll have to capture it binding those values as well.
>
> Hopefully I'm answering that right.
>

Thank you so much. Yes apparently django (and not the python sqlite3
module) defines the function. Tim Graham from the django list pointed
out that it occurs here:

https://github.com/django/django/blob/2f10216f84b55920de25422842a66260219e393f/django/db/backends/sqlite3/base.py#L173

Thanks so much this is very helpful and makes perfect sense!

Cheers,
Thomas
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users