Subject Re: [firebird-support] Re: Stored procedure - custom SQL
Author Robert martin

Thanks Adam. Thats just what I needed to know. It wont be a security
issue in this case and it does allow me to do what I need to do :)

I was worried I was going to have to scrap a reasonably large SP and do
back to the drawing board!

Rob Martin

Adam wrote:
> --- In
> <>, Robert martin <rob@...>
> wrote:
> >
> > Hi
> >
> > I am writing a stored procedure to replace some existing app code.
> >
> > As part of the procedure I get a list of tables that meet certain
> > criterion. I then want to run an update SQL on each of these
> tables. In
> > my code I built the SQL on the fly as shown below.
> >
> > i.e. 'UPDATE ' + fTableName + ' SET .........
> >
> > Can I do this in a SP (I suspect not)? Are there any other
> options, I
> > cant hard code the list of tables as they will change.?
> >
> You can do it. Whether you should or not is the real question. The
> EXECUTE STATEMENT command was added to PSQL in Firebird 1.5 which
> allows you to attempt to execute whatever you can place in a varchar
> variable. See release notes for details.
> You must be aware of the limitations though:
> * You open up a large security vulnerability. If I know you have a
> stored procedure that executes an update of an arbitrary field in an
> arbitrary table, you need to consider a malicious person passing in
> tablenames and fieldnames that they are not meant to (including
> system tables).
> * Performance will suffer. In this case, it would be no worse than
> your application logic. Normally, Firebird will cache the optimal
> plan of the particular statement inside a SP. This is impossible if
> it does not know the table and fields involved.
> * Parameters won't work. This is a real pain for such an approach,
> because you will need to cast everything and wrap things in quotes.
> Timestamps may also have formatting woes depending on date formats.
> Adam
> __.