How to capture Firebird SQL queries?

Question

Is there any way to capture SQL queries transmitted by old application created in Delphi/C++Builder + Firebird?

I don't have source code of that client app or access to (remote) database server.

Answer 1

Firebird 2.5 added the trace API which can be used to track prepare and execution of statements and a number of other things. The tools included in Firebird for use of the trace API are rather basic, but it might well be sufficient for your needs. Be aware that by default the trace API limits the size of statements captured and logged, and it might take some time to tweak the trace configuration to get all information you need.

An example configuration is:

<database mydatabase.fdb>
    enabled                true
    log_statement_prepare  true
    time_threshold         0
    max_sql_length         65536
</database>

Firebird 3.0 and higher use a slightly different syntax (with = between key and value):

<database mydatabase.fdb>
    enabled               = true
    log_statement_prepare = true
    time_threshold        = 0
    max_sql_length        = 65536
</database>

This should capture all statement prepares with the full SQL query in the database mydatabase.fdb.

See for more information: Audit and Trace Services in Firebird 2.5.

There are several vendors who provide tools that utilize the trace API (for example FB Tracemanager by Upscene Productions), and as already mentioned in the comments, there is also FBScanner by IBSurgeon which acts as a proxy between the client and a Firebird server and allows you to record the traffic (including statements).

Answer 2

Firebird includes a utility fbtracemgr.exe that can be used for tracing. Here's a sample command line:

cd "C:\Program Files\Firebird\Firebird_3_0"
fbtracemgr -start -service localhost/3050:service_mgr -config c:\temp\fb-trace.config -user sysdba -password <secret> >c:\temp\fb-trace.log

Discussion of parameters:

• The -start parameter instructs the tool to start a trace session. There are other parameters, just run fbtracemgr.exe without any arguments to see a list of possible parameters.

• The -service parameter tells the tool which service to trace. It is essential that you use the same connection method as the client that you want to monitor.

  • Let's say you use FlameRobin, in this case you probably have defined a database connection that uses TCP/IP and that connects to localhost and the default TCP port 3050. To match this you have to prefix the service name with "localhost/3050".
  • If you want to trace an isql.exe session, then you probably let isql.exe connect without using localhost. In this case you have to omit the "localhost/port" prefix and just specify -service service_mgr.

• The -config parameter specifies the path where the config file is located that contains the settings to be used for this trace session. Tracing must be configured with settings that define all the details of the trace, including what to trace. The settings can only be specified in the form of a configuration file.

  • The Firebird engine performs tracing of its own - the System Audit session. For this purpose it includes a trace configuration file located in its program folder. Use this file as an inspiration/template. It contains many commented options explaining purpose and syntax of each option. Filesystem location: C:\Program Files\Firebird\Firebird_3_0\fbtrace.conf.

• The -user and -password parameters are necessary only if you want to monitor a TCP/IP connection. If you want to monitor direct connections without authentication (e.g. isql.exe) then you can omit the credentials.

  • The user you specify for tracing must, obviously, have the rights to "spy" on the connection being traced.
  • The example uses "sysdba" which has of course all the rights. The user of the connection being traced should also be ok.

• The last part of the command redirects output to a trace log file. This is optional, but you'll probably want to do this because can be lots of output. You can open the trace log file in a text editor such as Notepad++ which will alert you when new content is written to the file.