commands vs SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

commands vs SP

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-05-27 : 16:22:35

Hello,
i had an argument with a colleague about runing sql commands in .net vs SP.

As a rule i always use SP to interact with databases and also to avoid sql Injections.

But my colleague said command objects in .net (postbacks) is as secure.

Any comments on this ???

Just asking

Afrika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-27 : 16:24:53

well speaking only from the point of sql injection then both are same if you use
parameters and not string contencation.

permissions on underlying objects are a different thing.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-27 : 17:17:11

If you "double the single quotes" in Dynamic SQL doesn't that also avoid SQL Injection?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-27 : 17:26:35

of course not.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-05-27 : 19:38:41

quote:
Originally posted by spirit1

well speaking only from the point of sql injection then both are same if you use
parameters and not string contencation.

permissions on underlying objects are a different thing.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Ok thanks,
but what do you think of direct commands to the db ?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-05-27 : 19:46:45

sorry continued here

... in .net using postbacks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 03:53:34

define what do you mean by direct commands.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 04:09:46

If you are using paramterized queries from .net, you don't have to worry about SQL Injection.

Some other criterias would be:
1. Amount of processing/calculations
2. Ease of deployment
3. Reusability
4. User permissions

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-05-28 : 05:23:18

"of course not"

Can you give me an example?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 07:27:13

you're right. my bad.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 07:32:22

quote:
Originally posted by Kristen

"of course not"

Can you give me an example?

Kristen

Check this link: [url]http://www.ngssoftware.com/papers/advanced_sql_injection.pdf[/url]

Goto Page 18, section [Strings without quotes]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-28 : 07:37:32

hey that's a nice article.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-28 : 16:12:00

OK, so if I "quote" everything I am forcing implicit conversion for numeric parameters. Have been doing that for years, and not considered the consequences of SQL having to make the conversion ...

... but no apparent "danger" ...

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 00:27:25

I believe using QUOTENAME() is the safest way rather than escaping the quotes.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-29 : 04:57:23

the "safest" way are sprocs

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-29 : 04:58:47

Good plan. I was thinking of double-quoting in the Application, but within SQL itself that's probably much more efficient than, say REPLACE(@MyString, '''', '''''') - quite apart from how hard that is to read!

QUOTENAME takes Nvarchar though, so restricted to 4,000 characters on SQL 2000 ... although I suppose the parameters to EXEC and sp_ExecuteSQL are Nvarchar too, so maybe not a restriction, as such ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-29 : 11:02:45

this might be of interest boys and girls:
http://www.security-hacks.com/2007/05/18/top-15-free-sql-injection-scanners

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources