Security Questions

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

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
 
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
 
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)
____________________________________________________________________________________________
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

Okay, the makes sense. Thanks.
 
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
 
Jonathan Wood said:
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?
Click to expand...

No, there are no problems related to security.
 
Back
Top