How do you sanitize a string for SQL

[Noobsa44]

I've just started lookin at using SQL for my windows application and I've started to consider how I should sanitize my string input. I would appreciate seeing both a quick and dirty way and the more secure, and probably more complex way (even if it's an API call).

The reason I want to see both methods is because I'm less worried that some one will try to wreck the DB on purpose (inner facing corp. app), but more worried that someone will do it accidentally. With this in mind, I care about performance slightly more than ensuring no attack could be successful, but if the quick/dirty method is just as fast as the more secure method, then I'll stick with security.

 

[imported_Dhaval00]

http://www.codeproject.com/cs/...qlInjectionAttacks.asp

 

[Markbnj]

Essentially: 1) validate your user input at the interface; 2) use parameterized queries and never dump any user string directly into a query string; 3) handle db exceptions gracefully.

Do those things and it is highly unlikely anyone can get a bad query to your db.

 

[KB]

The best way to sanitize strings is to use Stored Procedures.

 

[ArmchairAthlete]

use sql parameters

http://msdn2.microsoft.com/en-...ient.sqlparameter.aspx

I just use them all the time rather than worry about whether or not sql injection is possible.

 

[Noobsa44]

I've started using SQL parameters, however I wonder if they catch all cases. For example, do I still need to worry about a string like "This string won't be alright" where the single quote is not gracefully taken care of or can I remove my single quote fixing function? Thanks for all the helpful suggestions.

BTW KB, SQL Server 2005 Compact Edition does not support Stored Procedures, so I was not able to implement your suggestion.

For future reference, does anyone know if LINQ (VS 2008/.net 3.5) protects the database from SQL injections and/or invalid strings?

 

[Tarrant64]

Originally posted by: KB
The best way to sanitize strings is to use Stored Procedures.

Guy here at work is obsessed with stored procedures. He's happy with them due to the fact they kinda make things easier, and they're reusable.

 

[Markbnj]

Originally posted by: Tarrant64

Originally posted by: KB
The best way to sanitize strings is to use Stored Procedures.

Guy here at work is obsessed with stored procedures. He's happy with them due to the fact they kinda make things easier, and they're reusable.

I think a lot of them myself, but you have to keep it clear what should go in them. I think stored procs are applicable for logic needed to transactionally stitch together data from the database for application use. The app shouldn't need to own code that is specific to the database structure and implementation. Stored procs should not, imo, be used for business logic.