Hello,

How can I stop/prevent SQL server from running compound SQL
statements. I do not want the server to run multiple
update/delete/insert/select statements as a batch. Is there an option?

/Kaf
www.afiouni.com

Khaled Afiouni (post (AT) afiouni (DOT) com) writes:
No, there is no such option.

Please explain what your real problem is, and maybe we can find a
suggestion. What you are asking for right now does not really make sense?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

The usual mechanism to restrict what operations users can perform is to give
them access only through parameterized stored procedures. Does that not meet
your requirements?

--
David Portas
SQL Server MVP
--

Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote

Thank you for your reply. Please allow me to simplify it.

Actually I am checking for an ultimate solution to the SQL injection
issues. So in addition to filtering, checking and validating the
input, I would like to stop the compound statements from running and
allowing only the first SQL statements to be executed.

Any suggestions?

/Kaf
www.afiouni.com

Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote

Thank you very much for your reply.

I am trying to find an ultimate solution to the SQL injection issues.
In addition to verifying, validating and checking on the data entry
fields, I would like to prevent compound statements from running and
only allowing the first SQL statement to run.

Any Suggestions?

/Kaf
www.afiouni.com

Never write dynamic SQL; learn how to program correctly instead. This
is part of any basic Software Engineering course.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Khaled Afiouni (post (AT) afiouni (DOT) com) writes:
To do that you would have to add some middleware and have all your
clients talk to that middleware, and this middleware would pass the
code to SQL Server after validation and then pass the data back.

Not for the faint of heart. And it would be a reduction in usability,
since there sometimes be very good reason for an application to submit
two commands one go.

And you would not even be safe. You could intercept dynamic SQL created
client side, but not dynamic SQL created in stored procedures.

First step, is to let the users run the application with as few permissions
as possible. Ideally, all access should be through stored procedures, and
there should not be any dynamic SQL in the SPs as well. The users only
needs EXEC permission to the procedures. Now, this may hamper usability,
since some functions are easier to implement with dynamic SQL, not the
least if you want performance. (Typically this is search functions where
the users can search on a number of criterias.) But if you restrict
access to SELECT on the table, an intruder cannot wreck your database.

Next step is to write the SQL code properly. If you are constructing
SQL code client-side, use prepared statements with placeholds for
the parameters. Never build the entire string with values and all.
You can also call sp_executesql directly through RPC methods, *not*
as EXEC statements!

If you use dynamic SQL in stored procedures, use sp_executesql to run
your dynamic SQL, not EXEC().

For dynamic SQL on the client side, I have some articles on my web site:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

After following this thread for a few go-rounds, perhaps it is worth asking,
what is it that you are trying to achieve by stopping such compound
commands?
In particular, why is there SQL outside your control being posted to your
server?
No criticism of your system, just feels like your respondents could use a
"bigger picture".

"Khaled Afiouni" <post (AT) afiouni (DOT) com> wrote

Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote



Thank you for taking the time to write that helpfull reply. I appreciate it.

/Kaf
www.afiouni.com

"Mischa Sandberg" <mischa_sandberg (AT) telus (DOT) net> wrote


You are absolutely right. Allow me to share those documents with
everybody.

cnscenter.future.co.kr/resource/rsc-center/vendor-wp/Spidynamics/WhitepaperSQLInjection2.pdf

http://jo.morales0002.eresmas.net/ot...LInjection.pdf

This issue can get scary especially that I did see it in action :-)

/Kaf
www.afiouni.com