Subject RE: [firebird-support] Building statements at runtime
Author Alan McDonald
> Hi again,
> while writing my main question (rights-related, see the other message) I
> realized I am not quite sure about Firebird's handling of EXECUTE
> I am building a query in a stored procedure:
> CREATE PROCEDURE show_products (
> key INTEGER,
> name VARCHAR(100),
> ...
> ) AS
> ...
> query = 'SELECT * FROM products WHERE (1=1)';
> IF (key IS NOT NULL) THEN query = query || ' AND keyfield=' || key;
> IF (name IS NOT NULL) THEN query = query || ' AND name=''' ||
> name || '''';
> ...
> The question is, what happens if a user enters an apostrophe in the name
> field? Will Firebird correctly escape it and create a query such as "...
> WHERE name='abc''def' ..." or will I get a rather undesirable
> result of "...
> WHERE name='abc'def' ..."? And if it is the second case, as I
> suspect, how
> can I escape the apostrophes myself from within a stored
> procedure? It would
> be easy enough to write a custom function and store it in a UDF, but that
> would be extremely awkward (if I had to do this, it would almost seem
> preferable to rewrite the SELECTs not to use EXECUTE STATEMENT at
> all, but
> rather use many different SELECTs based on which parameters are not null)
> Thanks,
> Pepak

there's no magic escaping so you have to recognise, now, why there is all
our constant noise over the dangers of using EXECUTE STATMENT. It's there
for you to use but it's definteily not something I would let users provide
code for. What a delight your app would be for people wishing to experiment
with SQL injection.