| Subject | Re: [ib-support] Executing DSQL inside a stored procedure |
|---|---|
| Author | Jaume Andreu Sabater Malondra |
| Post date | 2002-09-08T00:37:23Z |
On Sunday 08 September 2002 02:01, Alan McDonald wrote:
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.
(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.
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.
--
Jaume Andreu Sabater Malondra
jsabater@...
Registered linux user #209072
"Ubi sapientas ibi libertas"
> you have me very curious as to why you would be trying this method of usingWell, in a few words, I have a stored procedure which receives a set of
> dynamic SQL. I'm not sure where to start...
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 wonderI have obtained all the information from Language Reference of Interbase 6.0
> why it objects to this. The prepare instruction is something a client needs
(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 buildingWell, I admit it may look "strange", but you have my word it deserves the work
> 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?
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
jsabater@...
Registered linux user #209072
"Ubi sapientas ibi libertas"