Re: Security Questions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I expect you're concerned with SQL injection attacks and you should be. However, when you bring in @Message as text (I would use VarChar(max)), I expect that you think that it opens the door for a SQL injection attack. I would try it. Notice that no matter what @Message contains, the WHERE clause is still used. That's because the SQL being executed is not concatenated on the fly.

The problem with hard-coded SQL is that it makes the application dependent on the schema. WHEN the schema changes, you're back to opening up N projects that might have referenced the schema in some specific way. And no, Visual Studio does not help here very much but keeping SQL in the SPs means that you can often make the change without changing the applications--as long as the SP signature does not change.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Jonathan Wood" <jwood@xxxxxxxxxxxxxxxx> wrote in message news:%23bLtce1TIHA.3532@xxxxxxxxxxxxxxxxxxxxxxx
I have a couple of security questions.

First, I just created the following stored procedure:

ALTER PROCEDURE dbo.mc_Trainer_SetClientMessage
@TrainerId uniqueidentifier,
@Message text
AS
BEGIN
UPDATE dbo.mc_Trainers SET WelcomeText=@Message WHERE UserID=@TrainerId
RETURN
End

Am I correct in my understanding that, if @Message contains any SQL statements, that will not cause any problems within this procedure?

Second, does anyone see any problems using hard-coded SQL statements in C# as long as such statements are constructed without any user-entered text?

Thanks for any suggestions!

Jonathan


.

Relevant Pages

  • Re: Decouple SQL queries from class in OOP design
    ... > classes and bound them to the local database schema. ... top" of the SQL statements will not survive any major schema changes ... > now is to create a hash to store all the SQL statements and prepare ... When you add functions to your application you both have to edit ...
    (comp.object)
  • Re: [Help] How to make getText() return the result in case sensitive ?
    ... It's cleaner, more efficient, and most importantly, protects against SQL injection attacks and bugs. ... You should basically never be constructing an SQL string in an app, unless you have a very good reason indeed. ... In either case, there is no reason to put unescaped SQL into a String to be executed, so PreparedStatement isn't really optional, regardless of the most important reason;-) ...
    (comp.lang.java.programmer)
  • Re: SQL beginner help
    ... SQL injection attacks can be nasty. ... SQL server is or be able to modify any SQL code in the page source? ... SQL injection typically involves a hacker entering something like the ...
    (microsoft.public.sqlserver)
  • Re: sql syntax error....
    ... you're creating an application that will be subject to SQL injection attacks. ... I suggest re-writing this query to accept parameters instead of concatenated strings as arguments. ... This solves both problems and reduces the chance that the contents of the TextBox.Text string will break the SQL syntax. ... retrieve values. ...
    (microsoft.public.vb.database.ado)
  • Stored Procs and SQL Injection Attacks...
    ... We have been asked by a client to comment on the ... vulnerability of a system to SQL injection attacks and one ... versus dynamic SQL. ...
    (microsoft.public.sqlserver.security)