Hi

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 firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, 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
>
> __.