Security Questions

[Jonathan Wood]

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

 

[Cor Ligthert[MVP]]

Jonhathan,

First you bring this as Security Question. Is this real a Security Question
or just a coding question?

I don't see any relation to Security.

As it is about code, then you miss at least any concurrency check so this is
where the latest wins always.

While a SQL text script in the Net class is never checking for any dataname
change in your database.

Cor

 

[Jonathan Wood]

Cor,

First you bring this as Security Question. Is this real a Security
Question or just a coding question?

Security. Although, I'm open to comments on the code.

I don't see any relation to Security.
http://www.google.com/search?hl=en&...z=1I7GGLD&q=sql+injection+attacks&btnG=Search

As it is about code, then you miss at least any concurrency check so this
is where the latest wins always.

I just don't see how that could be a problem here.

 

[William Vaughn]

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]

William,

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.

No, my original post asked if I was correct in thinking that setting text in
a stored procedure this way *prevented* the possibility of a SQL injection
attack. I'm trying to confirm my thinking.

I'll consider using VarChar(max) instead of text, although I'd probably need
a reason to change it.

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.

I'm not 100% sure if the WHERE clause would be the only possible
vulnerability. If I'm understanding, you seem to be confirming that, indeed,
stored procedures like the one I posted are not vulnerable to injection
attacks.

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.

Okay, the makes sense. Thanks.

 

[Cor Ligthert[MVP]]

Jonathan

AFAIK are SQL injections based on replacing the text between quotes.
As far as I can see do you not use quotes in your SQL string

By the way, you show it yourself, why are you then asking this here?

Cor

 

[Miha Markic]

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?
Correct.


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?

No, there are no problems related to security.

 

[Jonathan Wood]

Thanks!