If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > SQL Injection and SPs

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
SQL Injection and SPs

Hi,
I need some information from someone that is an expert in MSSQL and SQLI.
I wanted to know if SQLI is possible through systematic stored procedures of MSSQL?

Thanks,
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,789
SQL Injection occurs because of an insecurely written app running on the client. Nothing you can do on the SQL Server can affect SQL Injection because it has already occured before SQL Server even receives the SQL statement from the SQL Client.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 298
Your best bet is to keep the interface as procedure calls between the DB and the application code. If the procedure is properly written, it will disallow bad data, free text, etc.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
Thanks to both replies.
I have a question for Celko.
You mean that systematic SPs are safe from injection?
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,789
If your front end code is vulnerable to SQL Injection, then any database is vulnerable to SQL Injection (even if the database doesn't run SQL at all).

Any application that sends unchecked, unprotected strings to a database server is vulnerable to injection. It doesn't matter what the developer thought that the code ought to do/run/etc. That isn't relevant.

All code, of any kind on a database server is vulnerable to injection attacks from a weak front end application.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
Thanks Pat.
I agree with you but shouldn't systematic SPs be secure? because we have vulnerability where ever human (programmer) comes in the middle.
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Quote:
Originally Posted by Pat Phelan View Post
SQL Injection occurs because of an insecurely written app running on the client. Nothing you can do on the SQL Server can affect SQL Injection because it has already occured before SQL Server even receives the SQL statement from the SQL Client.

-PatP
Not "quite" true...
...you could disallow all access to the database tables except through sprocs and views.

I've yet to see an application architect willing to give up these privileges without a fight, though.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
If your front end does not use command and parameter objects to encapsulate the arguments of the stored procedure, and instead simply concatenates the stored procedure and arguments as a single string, you have a SQL injection vulnerability, no matter what procedure is being called, "systematic" or otherwise.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
Thanks,
Do you think I could find a list of systematic SPs that could be vulnerable to injection or I have to manually search?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
Define "systematic", please.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
I mean the system SPs (with the sp_ or xp_ prefix) of MSSQL.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
Anything that the application user has access to is available in a SQL Injection attack. If you run your application as sa, or another admin account, all of them could potentially be invoked. As could any statement like "drop database msdb", or "create backdoor login..."
Reply With Quote
  #13 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
To prevent SQL Injection you would need to lock down all your tables, allow access only through direct calls to stored procedures, and ensure that none of your stored procedures issue dynamic sql statements with concatenated strings created from either procedure parameters or character-based data columns.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
Thanks to everyone that has sent an answer.
Many thanks,

I wanted to ask blindman what do you mean by direct call of SPs?
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 35
Quote:
Originally Posted by blindman View Post
To prevent SQL Injection you would need to lock down all your tables, allow access only through direct calls to stored procedures, and ensure that none of your stored procedures issue dynamic sql statements with concatenated strings created from either procedure parameters or character-based data columns.
What do you mean by direct call to Sp?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On