| Subject | Re: Stored procedure - custom SQL |
|---|---|
| Author | Adam |
| Post date | 2006-06-21T02:22:15Z |
--- In firebird-support@yahoogroups.com, Robert martin <rob@...>
wrote:
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
wrote:
>tables. In
> 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
> my code I built the SQL on the fly as shown below.options, I
>
> i.e. 'UPDATE ' + fTableName + ' SET .........
>
> Can I do this in a SP (I suspect not)? Are there any other
> 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