Proper use of parameters in FirebirdSql

Question

I wonder if anyone can help with the following.

using (FbConnection conn = new FbConnection(ConnectionString))
{
    conn.Open();

    // --------------------------------------------------------------------------
    FbCommand command1 = new FbCommand("SELECT @AN_INT FROM RDB$DATABASE", conn);
    command1.Parameters.Add("AN_INT", FbDbType.Integer);
    try
    {
        command1.Prepare();  //  Message=Dynamic SQL Error
                            //SQL error code = -804
                            //Data type unknown
                            //  Source=FirebirdSql.Data.FirebirdClient
                            //  ErrorCode=335544569
                            //  SQLSTATE=42000
    }
    catch(Exception E)
    {
        MessageBox.Show(E.Message);
    }
    // --------------------------------------------------------------------------
    FbCommand command2 = new FbCommand("SELECT 123 FROM RDB$DATABASE WHERE 789 >= @AN_INT", conn);
    command2.Parameters.Add("AN_INT", FbDbType.Integer);
    try
    {
        command2.Prepare();  // No Problem
    }
    catch (Exception E)
    {
        MessageBox.Show(E.Message);
    }
}

My problem is this - I have picked up a project from another coder and I think that, if possible, I should change the database component to use parameterised queries; the existing technique is to inject values into Sql strings. The task is to refactor a class to work in an existing project.

The code sample above demonstrates one problem that I must resolve and I wonder of there are others. The issue is, essentially, to create a class that will turn strings into parameterised queries. Has anyone done this, and what traps or tricks might there be along the way?

Answer 1

Your first query needs to be SELECT cast(@AN_INT as int) FROM RDB$DATABASE. Else Firebird doesn't know what the parameter type is (even if it's specified in C# code).

You can try to run this piece of code directly in Firebird to see the limitation of engine itself.

execute block
as
begin
    execute statement ('select :foobar from rdb$database')(foobar := 10);
end