Stored procedures - when to use?

[Square Eye]

Hello

I'm a self-taught ASP developer who never found out about stored procedures until recently. I've got a hacker trying to put me out of business, livelihood, psychological health etc. by repeatedly attacking my database with SQL injections. So I have thousands of pages on 30 websites to go through and recode, using SPs.

An easy - and probably stupid question - do I need to use SPs when there is no user variable involved? i.e. I know they're required when I want to

"SELECT Field FROM Table WHERE ID =" & Request("Variable")

But is the following format OK, or should it be converted to an SP too?

"SELECT Field FROM Table "

Thanks for any guidance!

Square

[Newsgroup_User · Accepted solution]

> An easy - and probably stupid question - do I need to use SPs when there
> is no
> user variable involved? i.e. I know they're required when I want to

The basic premise of SQL injection is that it takes end-user inputted
strings and sends it directly to SQL.

NEVER trust your end users. ;o)

So, in the example you gave, you're not grabbing any user-created strings
and passing it along, so no, one can't SQL inject that.

You can avoid SQL injection without SPs, but it takes a lot of work. You
need to filter all user input first to ensure it is 'clean' of malicious
code, and then pass it on. It's usually easier to just use SPs, so that's
usually the stock answer.

-Darrel

[Square Eye]

Thanks Darrel! And yes, I meant 'SPs are useful' rather than 'SPs are required' - but seems like the way to go after this hacker has picked his way through standard DW8-written coding, which I assumed would be safe...

Much appreciated, you've saved me some sleep...

[Newsgroup_User]

You don't neccessarily need to use stored procedures. The recordsets in CS3
use parameterised queries, thus eliminating SQL injection.

--
Jules
http://www.charon.co.uk/products.aspx
Charon Cart
Ecommerce for ASP/ASP.NET

[MikeL7]

I am also debating switching to SP's, i have a Intranet site at work using ASP and have all the recordsets using parameters. I had a couple hand coded RS's without parameters, and when users would enter a single quote the RS would fail.
Are the Parameterized queries in DW CS3 ASP really safe from SQL injection?
I have tried doing some injection attacks i read about on the test DB and haven't been able to Drop any tables or the database

[edglenning]

Keep in mind aside from the benefits noted above...Stored Procedures are code that is compiled once when it is created. From then on the code is just executed by the DBMS. When you execute a query embedded in your application that query is compiled by the DBMS every time it is executed. So to increase speed for users still on lower speed links and to reduce overhead on your database server stored procedures work very well for higher volume queries. To see the benefits realtime you can turn SQL Tracing on and you will see much less data exchanged between the client and server with a stored proc. Also detailed debuging or if you have MSSQL you can do a show plan and statistics IO which will also show efficiency gains. Good Luck

[Newsgroup_User]

> Another and much more powerful option are prepared statements, but it
> depends on the DB and language if and how you can use them. I can't help
> you here with ASP, because I'm on PHP.

I don't recall what that's called in ASP, but if the OP is thinking of
upping to ASP.net, they're called 'parameterized queries'.

-Darrel