Subject Re: [ib-support] Executing DSQL inside a stored procedure
Author Jaume Andreu Sabater Malondra
On Sunday 08 September 2002 02:01, Alan McDonald wrote:

> you have me very curious as to why you would be trying this method of using
> dynamic SQL. I'm not sure where to start...

Well, in a few words, I have a stored procedure which receives a set of
parameters and, depending of which of them are set and which not as well as
depending on the values they have, the UPDATE statement that must be executed
is different (the WHERE condition changes).

This is not the first time I use the "methd", but previous times I used it on
Oracle, about three years ago.

> AFAIK, there is no such datatype as "STATEMENT", so there's little wonder
> why it objects to this. The prepare instruction is something a client needs

I have obtained all the information from Language Reference of Interbase 6.0
(the PDFs). But I was using it in a wrong way. I mean, Interbase is not able
to execute DSQL inside a string in a trigger or a stored procedure, as Bill
Todd has told me in Borland Interbase newsgroups. So, I guess the problem is
the same here in Firebird.

> What I really wonder about is why you are going to so much trouble building
> the statement client side, then saving it into a table column, then
> committing it then trying to execute it inside a SP... If you are building
> it client side anyway, why wouldn't you just execute it from the client?

Well, I admit it may look "strange", but you have my word it deserves the work
and there are good reasons behind it. I'll briefly explain it:

1. I am using Firebird from Php under Apache on my Linux server in a web site.

2. The database user which is used on Php scripts only have got permissions
over a set of stored procedures.

3. These stored procedures are created to solve the needs of the whole bunch
of pages that form the web site.

4. You want a dataset with news? You call a stored procedure from Php code and
pass the necessary parameters to it (date, first and skip values, etc.).

5. The database user does not know tables exist (unless there is something I
cannot do with stored procedures, like this case), so security is increased
and, in case the web site security is compromised and a cracker gets the
database username and password from Php scripts, the worst thing he or she
can do is execute the stored procedures.

6. This, combined with the fact that _ALL_ the stored procedures call a
"primary/basic" stored procedure (which is only readable by those stored
procedures, but not by the database user that executes the above ones) that
verifies the connection is valid/registered, gives a new level of protection
to the web site. The cost if disk accesses are increased, of course.

> A mystery inside an enigma perhaps - I'm sure you will enlighten us...

I hope I have.

Jaume Andreu Sabater Malondra
Registered linux user #209072

"Ubi sapientas ibi libertas"