Actual SQL statement after bind variables specified

Question

I am trying to log every SQL statement executed from my scripts. However I contemplate one problem I can not overcome.

Is there a way to compute actual SQL statement after bind variables were specified. In SQLite I had to compute the statement to be executed manually, using code below:

def __sql_to_str__(self, value,args):
    for p in args:
        if type(p) is IntType or p is None:
            value = value.replace("?", str(p) ,1)
        else:
            value = value.replace("?",'\'' + p + '\'',1)
    return value

It seems CX_Oracle has cursor.parse() facilities. But I can't figure out how to trick CX_Oracle to compute my query before its execution.

score 7 · Accepted Answer · answered Jan 14 '11 at 17:34

7

The query is never computed as a single string. The actual text of the query and the params are never interpolated and don't produce a real full string with both.

That's the whole point of using parameterized queries - you separate the query from the data - preventing sql injections and limitations all in one go, and allowing easy query optimization. The database gets both separately, and does what it needs to do, without ever joining them together.

That said, you could generate the query yourself, but note that the query you generate, although probably equivalent, is not what gets actually executed on the database.

answered Jan 14 '11 at 17:34

nosklo

217,122
57
293
297

I only disagree with your data injection point. One could inject the whole damn query since its kind of dynamic by nature. Otherwise thank you very much for putting a closure on this subject. – bioffe Jan 14 '11 at 17:48
1

@bioffe: Well, that's where the programmer enters - by developing applications with only static queries and parameterized attributes, you avoid the injections and speed up the application a lot, since Oracle caches pre-compiled queries if the text is the same. – nosklo Jan 14 '11 at 18:06
it's very hard to achieve if you have a dynamic entity object composition. I understand that the security concerns should be addressed by the framework provider, but they always give their customers more granular low-level control of an entity composition and persistence. – bioffe Jan 14 '11 at 18:14

score 1 · Answer 2 · answered Jan 14 '11 at 17:38

Your best bet is to do it at the database server, since a properly implemented Oracle connector will not put the bind variables into a string before sending the query to the server. See if you can find an Oracle server setting that makes it log the queries it executes.

score 0 · Answer 3 · answered Jan 14 '11 at 19:31

0

You might want to consider using Oracle's extended SQL trace feature for this. I would recommend starting here: http://carymillsap.blogspot.com/2011/01/new-paper-mastering-performance-with.html.

answered Jan 14 '11 at 19:31

Dave Costa

47,262
8
56
72

Actual SQL statement after bind variables specified

3 Answers3

Linked