Compound Statements

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.comKhaled Afiouni (post@.afiouni.com) writes:
> 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?

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../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@.sommarskog.se> wrote in message news:<Xns951F7F821DCD5Yazorman@.127.0.0.1>...
> Khaled Afiouni (post@.afiouni.com) writes:
> > 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?
> 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?

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@.sommarskog.se> wrote in message news:<Xns951F7F821DCD5Yazorman@.127.0.0.1>...
> Khaled Afiouni (post@.afiouni.com) writes:
> > 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?
> 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?

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|||>> 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!|||Khaled Afiouni (post@.afiouni.com) writes:
> 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.

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../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@.afiouni.com> wrote in message
news:a5c90178.0407070152.17be0506@.posting.google.c om...
> 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|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951FEBF2D734Yazorman@.127.0.0.1>...
> Khaled Afiouni (post@.afiouni.com) writes:
> > 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.
> 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

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

/Kaf
www.afiouni.com|||"Mischa Sandberg" <mischa_sandberg@.telus.net> wrote in message news:<km1Hc.11732$eO.2611@.edtnps89>...
> 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.com> wrote in message
> news:a5c90178.0407070152.17be0506@.posting.google.c om...
> > 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

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/o...QLInjection.pdf

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

/Kaf
www.afiouni.com|||Joe Celko <jcelko212@.earthlink.net> wrote in message news:<40ec4e5d$0$16505$c397aba@.news.newsgroups.ws>...
> >> 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!

O:-)

/Kaf
www.afiouni.com|||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.com> wrote in message ...
cnscenter.future.co.kr/resource/rsc-center/vendor-wp/Spidynamics/WhitepaperS
QLInjection2.pdf
> http://jo.morales0002.eresmas.net/o...QLInjection.pdf
> This issue can get scary especially that I did see it in action :-)
> /Kaf
> www.afiouni.com

Complex delete query

Is this valid query:
select a.ID_Attachment FROM attachment a
INNER JOIN message_attachment b ON a.ID_Attachment = b.ID_Attachment
INNER JOIN message c ON b.ID_Message = c.ID_Message
WHERE c.ID_SOMETHING = 1
?Assuming that the tables exists, yes. But I don=B4t understand your
subject within this case ?!
HTH, jens Suessmeyer.|||How about this:
DELETE FROM attachment a
INNER JOIN message_attachment b ON a.ID_Attachment = b.ID_Attachment
INNER JOIN message c ON b.ID_Message = c.ID_Message
WHERE c.ID_SOMETHING = 1
Thank you. :)|||On Wed, 23 Nov 2005 19:08:54 +0100, Petar Popara wrote:

>How about this:
>DELETE FROM attachment a
>INNER JOIN message_attachment b ON a.ID_Attachment = b.ID_Attachment
>INNER JOIN message c ON b.ID_Message = c.ID_Message
>WHERE c.ID_SOMETHING = 1
>Thank you. :)
>
Hi Petar,
This is not a valid syntax.
I *think* that you want something like
DELETE FROM attachment
WHERE EXISTS
(SELECT *
FROM message_attachment AS b
INNER JOIN message AS c
ON b.ID_Message = c.ID_Message
WHERE c.ID_SOMETHING = 1
AND attachment.ID_Attachment = b.ID_Attachment)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Complex Delet Query .. Help me!

I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.have you tried ON DELETE CASCADE in the foreign key relationship?

i've never done this myself (cascading delete in the adjacency model hierarchy), so i'd be very interested in finding out that it works|||I have not had much SQL experience.. Im more of a C#/C++/asp.net developer! Moreover Iam deleting the records in the same table.. so there is no foriegn key or anything here.. did I miss something!?|||create table yourtable
( SECID integer
, PARENTID integer
, NAME varchar(50)
, primary key (SECID)
, constraint validparent
foreign key (PARENTID)
references yourtable (SECID)
on delete cascade
)

-- load table

delete from yourtable where SECID=8001

Edit: nope, that don't workError: Introducing FOREIGN KEY constraint 'validparent' on table 'yourtable' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (State:37000, Native Code: 6F9)
Error: Could not create constraint. See previous errors. (State:37000, Native Code: 6D6)|||you'll have to do a recursive user define function...

(working on this RUDF..)|||Originally posted by netjkus
I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.

This is a complex query. I never heard or came accross like this. The parent-->child is nesting many times. I am not sure you can do it in one SQL. Might have to use a store procedure and keep the nodes in a temp table. Delete from the main table until no records found in temp table|||Check this one - with triggers (they have to be recursive - database option)...

drop table test
create table test (id int primary key,pid int)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
insert test values(4,2)
go
select * from test
go
create trigger tr_test on test
for delete
as
if not exists(select * from deleted)
return
delete test where pid in(select id from deleted)
go
delete test where id=2-- or 1|||smasanam

I'm almost there with my RUDF...
just a few minutes more|||GOT IT GOOD

coming up !|||Create these functions

CREATE FUNCTION Trim (@.Mot Varchar(230))
RETURNS Varchar(230) AS
BEGIN
return(Rtrim(Ltrim(@.Mot)))
END

CREATE Function ListOfChildren (@.Parents Varchar(100))
RETURNS Varchar(100)
As
Begin
Declare @.List Varchar(100),
@.NewList VarChar(100),
@.TotalList VarChar(100)

set @.List=@.Parents
set @.NewList='abc'
set @.TotalList='|' + @.Parents

while @.NewList<>''
begin
set @.NewList=''
Select @.NewList = @.NewList + '|' + dbo.Trim(SectID) From Family Where PatIndex('%' +dbo.Trim(ParentID)+ '%', @.List )<>0
set @.List= @.NewList
set @.TotalList=@.List+@.TotalList
end

Return(@.TotalList)
End|||then you can do :

delete family
where patindex('%' + dbo.Trim(sectid) +'%' , dbo.listofchildren('8007'))<>0 or
patindex('%' + dbo.Trim(parentid) +'%' , dbo.listofchildren('8007'))<>0

in fact the function ListOfChildren returns for 8007 the list of :
all children (8009,8010)
all children of children (8011)
the named parent (8007)|||http://www.sqlteam.com/item.asp?ItemID=8866

Or

http://archives.postgresql.org/pgsql-sql/2002-11/msg00358.php|||my solution seems simpler than Brett's...

wooooooooo
risky saying this|||Karolyn

Thanks a ton.. Iam trying this right now...|||Originally posted by Karolyn
my solution seems simpler than Brett's...

wooooooooo
risky saying this

troublemaker, heh?

Anyway isn't

while @.NewList<>''
begin
set @.NewList=''

False right away?|||nope 'cause it's set to 'abc'

need gllasssseesss ?|||(ding ding ding)
ROUND 1

Karolyn : right strong punch
Brett : KO|||and when you do the total

Brett : 1289
Karolyn : 1

catching up !!!|||How right you are...the evaluation doesn't come until the END...|||I can't answer lots of question
so when I do
I make a big fuss out of it
(a Frenchy-of-France-Attitude that I learned here in France)|||BUT STILL...
your

code:------------------------
while @.NewList<>''
begin
set @.NewList=''
------------------------
False right away?


was a VERY Low one|||Very nice...

USE Northwind
GO

CREATE TABLE family(SECtID varchar(10), PARENTID varchar(10))
GO

INSERT INTO family(SECtID, PARENTID)
SELECT '8000', NULL UNION ALL
SELECT '8001', '8000' UNION ALL
SELECT '8002', '8000' UNION ALL
SELECT '8003', '8002' UNION ALL
SELECT '8004', '8002' UNION ALL
SELECT '8005', '8003' UNION ALL
SELECT '8006', '8003' UNION ALL
SELECT '8007', '8001' UNION ALL
SELECT '8008', '8001' UNION ALL
SELECT '8009', '8007' UNION ALL
SELECT '8010', '8007' UNION ALL
SELECT '8011', '8009'
GO

SELECT dbo.ListOfChildren(8007)
GO

DROP FUNCTION TRIM
DROP FUNCTION ListOfChildren
DROP TABLE family
GO

And I like the fact that is comes out in asceding hierarchy, with the grandfather of it all last...|||Originally posted by Karolyn
BUT STILL...
your



was a VERY Low one

Well I apologize if I offended you.

Listen, that's why I'm a scrub...I missed it...

(had nothing to do with trying to put you down...a very silly game I don't play)|||Karolyn

Thanks a ton.. It works!! You made my day!! Iam implementing it !

compensating delete on replication database!

Hi
I have Merge Publication with just one Subscriber running on SQL 2000
which has been Service packed fully.
Looking at the previous posts I did uncheck 'Enforce relationship for
replication' on the Table Relationships hoping that the deletion of
records will not happen.
But for some unknown reason the records in the FK relationship is
deleted, which is causing all sorts of problems. There are no Triggers
on the database, and I could not find a pattern for the deletion it
happens randomly.
E.G. We have a 'Contacts' and 'Addresses' Table, one contact can have
more than 1 address. But for some reason the addresses are being deleted
for the contact, i am not sure where it is happening whether in
Subscriber or Publisher, but we loose the record.
Does any one has a good solution for this?
Thanks in advance.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Do you have any user triggers at all?
Perhaps you are experiencing the compensating commands situation :
http://support.microsoft.com/default...Product=sql2k?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Thanks Paul for the link. But I do not have any user triggers at all?
Since the 'Enforce relationship for replication' is unchecked, I am a
bit puzzled about how this is happening.
Can I apply the hotfix (the link you have sent) to the production
server? Is it safe?
Thanks once again.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
The Hotfix is available only in SP4 Beta and not sure when it is going
to be released. In the meantime the database is loosing the records all
the way through. Can you suggest any intermediate solutions? somethings
I need to check etc..
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The two suggestions weren't related - user triggers was just one other
posibility.
If it is a compensation issue, you should be able to reproduce. An address
added successfully at one site will be deleted if it cannot be applied at
the other due to a PK/FK error, constraint error etc. So, you need to check
that the schemas are identical wrt these tables (SQLCompare?). If you can
find an address that repeatedly gets removed then tracking/removing the
constraint should be simple.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Hi Paul
Thanks for the reply. There is not pattern these records are deleted and
I am not able to re-create it as well.
The Addresses Table has 2 FK relationship one to the AddressType Table
and one to the Contact Table were the records are all present. It is
only the Address records that gets deleted.
Any other areas to look into, please...
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
I am really sorry, there is a Trigger created by another developer on
the Addresses Table, which maintaines the Ref. Intergerity. Could this
be the cause for this 'compensating deletion', if so how to overcome
this, do we need to remove the triggers?
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!