Compound Statements

[Khaled Afiouni]

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

https://www.afiouni.com

[Erland Sommarskog]

Re: Compound Statements

Khaled Afiouni (post@afiouni.c om) writes:[color=blue]
> 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?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: Compound Statements

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
--

[Khaled Afiouni]

Re: Compound Statements

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns951F7F 821DCD5Yazorman @127.0.0.1>...[color=blue]
> Khaled Afiouni (post@afiouni.c om) writes:[color=green]
> > 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?[/color]
>
> 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?[/color]

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

https://www.afiouni.com

[Khaled Afiouni]

Re: Compound Statements

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns951F7F 821DCD5Yazorman @127.0.0.1>...[color=blue]
> Khaled Afiouni (post@afiouni.c om) writes:[color=green]
> > 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?[/color]
>
> 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?[/color]

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

https://www.afiouni.com

[Joe Celko]

Re: Compound Statements

>> Actually I am checking for an ultimate solution to the SQL injection
issues. <<

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!

[Erland Sommarskog]

Re: Compound Statements

Khaled Afiouni (post@afiouni.c om) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mischa Sandberg]

Re: Compound Statements

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@afiouni.c om> wrote in message
news:a5c90178.0 407070152.17be0 506@posting.goo gle.com...[color=blue]
> 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[/color]

[Khaled Afiouni]

Re: Compound Statements

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns951FEB F2D734Yazorman@ 127.0.0.1>...[color=blue]
> Khaled Afiouni (post@afiouni.c om) writes:[color=green]
> > 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.[/color]
>
> 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[/color]



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

/Kaf

https://www.afiouni.com

[Khaled Afiouni]

Re: Compound Statements

"Mischa Sandberg" <mischa_sandber g@telus.net> wrote in message news:<km1Hc.117 32$eO.2611@edtn ps89>...[color=blue]
> 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@afiouni.c om> wrote in message
> news:a5c90178.0 407070152.17be0 506@posting.goo gle.com...[color=green]
> > 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[/color][/color]


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

cnscenter.futur e.co.kr/resource/rsc-center/vendor-wp/Spidynamics/WhitepaperSQLIn jection2.pdf

http://jo.morales0002.eresmas.net/otrospdfs/SQLInjection.pdf

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

/Kaf

https://www.afiouni.com

[Khaled Afiouni]

Re: Compound Statements

Joe Celko <jcelko212@eart hlink.net> wrote in message news:<40ec4e5d$ 0$16505$c397aba @news.newsgroup s.ws>...[color=blue][color=green][color=darkred]
> >> Actually I am checking for an ultimate solution to the SQL injection[/color][/color]
> issues. <<
>
> 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![/color]


O:-)

/Kaf

https://www.afiouni.com

[Mischa Sandberg]

Re: Compound Statements

I used to work for Simba.com (now owned by Orbital.com). Simba produced the
SQL engine/driver kit behind about half the ODBC drivers in the world.

We had a tiny ODBC proxy driver that did more or less what you were asking.
It received the SQL commands, applied the engine's parser to them, did some
rulechecking/rewriting of the parse tree based on the customer's
requirements, and either responded with an error, or forwarded the parse
tree (collapsed back to a command string) to the REAL ODBC connection.

You may want to check with Orbital on that.

"Khaled Afiouni" <post@afiouni.c om> wrote in message ...[color=blue]
>[/color]
cnscenter.futur e.co.kr/resource/rsc-center/vendor-wp/Spidynamics/WhitepaperS
QLInjection2.pd f[color=blue]
> 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[/color]