Don't ever use string concatenation (or a StringBuilder) to create SQL commands. An example is this:
string sql = "SELECT * FROM Products WHERE Category=" + cat;
There are a lot of reasons why not to do this:
1. Strings inside the command text needs to be enclosed between ' and '. You can have a problem when the value of cat contains a ' itself. You can avoid this by doubling all single quotes inside the cat string, but it still is not recommended.
2. SQL Injection attacks!!! Don't be tricked by this one, it's easy to avoid. Think of a string cat that contains the following value:
1; DROP TABLE Products; --
-- is the comment operator in T-SQL. So, the resulting command is this:
SELECT * FROM Products WHERE Category=1; DROP TABLE Products; --
The result: the Products table is droppe. Thus, pretty simple to do if the cat value comes from the querystring or from a form input.
How to avoid this:
1. Don't ever ever connect to the database as "sa" or another db owner with full access to the underlying database. Always connect with the least privileges needed to do the job.
2. Don't use string concat, but use parameterized commands instead, like this:
string query = "SELECT * FROM Products WHERE Category=@Category";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 50);
cmd.Parameters["@Category"].Value = cat;
//...
This will make sure the anomalities with quotes are solved for you, as well as avoid basic injections and perform checkings for the input length of the
strings (+ type checking etc). 3. Even better, use a stored procedure with parameters on the server and call it using SqlCommand. The idea is the same, but the SQL command with params itself is stored on the server. This allows better performance and even
better security.
Why not write up your notes into an article for www.codeproject.com?
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
Like you I wish there some guidance on hack strings, so we can include them in the general abuse we include in test scripts.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
Naom>Just to make sure - we need to add the information into web.config file for <system.web>
Which information please?
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
bdesmet
Star
8255 Points
1651 Posts
Avoid SQL Injection attacks
Dec 19, 2004 03:32 PM|LINK
string query = "SELECT * FROM Products WHERE Category=@Category"; SqlCommand cmd = new SqlCommand(query, conn); cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 50); cmd.Parameters["@Category"].Value = cat; //...This will make sure the anomalities with quotes are solved for you, as well as avoid basic injections and perform checkings for the input length of the strings (+ type checking etc). 3. Even better, use a stored procedure with parameters on the server and call it using SqlCommand. The idea is the same, but the SQL command with params itself is stored on the server. This allows better performance and even better security.Visit www.msdn.be, www.bartdesmet.net
joydipkanjil...
Member
101 Points
25 Posts
Re: Avoid SQL Injection attacks
Mar 07, 2008 07:44 AM|LINK
Good post.
Here is my article on this: http://www.aspnetpro.com/newsletterarticle/2006/12/asp200612jk_l/asp200612jk_l.asp
Best,
Joydip
Author: ASP.NET Data Presentation Controls Essentials (Packt Publishing)
http://www.amazon.com/ASP-NET-Data-Presentation-Controls-Essentials/dp/1847193951
Microsoft Most Valuable Professional (ASP.NET)
http://aspadvice.com/blogs/joydip
uwspstar
Member
740 Points
215 Posts
Re: Avoid SQL Injection attacks
Apr 10, 2008 02:25 PM|LINK
it is a nice for list this topic, if you could post a vidoe stuff to explain what you did here, it will be great !
If you mark as "Answer"other people can use this answer as a reference
yeotumitsu@s...
Contributor
4907 Points
836 Posts
Re: Avoid SQL Injection attacks
Apr 12, 2008 07:53 PM|LINK
always use sql helper n oracle helper to be in touch wid any database.
error handling is easy n less eror pron.
bind parameters with command object.
n use properties to avoid sql injection.
-Manas
=======================================
If this post is useful to you, please mark it as answer.
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: Avoid SQL Injection attacks
Apr 12, 2008 08:03 PM|LINK
Why not write up your notes into an article for www.codeproject.com?
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
mod84
Participant
797 Points
275 Posts
Re: Avoid SQL Injection attacks
Apr 13, 2008 06:08 AM|LINK
also avoid the 'exec' sql command
My .NET Free Code Library
mbanavige
All-Star
134971 Points
15423 Posts
ASPInsiders
Moderator
MVP
Re: Avoid SQL Injection attacks
Apr 13, 2008 03:48 PM|LINK
exec in and of itself is not necessarily unsafe. rather it is how exec is used that can open you up to injection attacks.
for example: http://www.dotnetjunkies.com/WebLog/chris.taylor/archive/2004/10/13/28370.aspx
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: Avoid SQL Injection attacks
Jul 22, 2008 11:10 AM|LINK
There is an excellant article on SQL Injection attacks at http://forums.asp.net/t/1254125.aspx
Like you I wish there some guidance on hack strings, so we can include them in the general abuse we include in test scripts.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
Naom
All-Star
36004 Points
7901 Posts
Re: Avoid SQL Injection attacks
Jul 22, 2008 10:13 PM|LINK
Thanks for the link. Just to make sure - we need to add the information into web.config file for <system.web>, right?
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: Avoid SQL Injection attacks
Jul 23, 2008 05:09 AM|LINK
Naom>Just to make sure - we need to add the information into web.config file for <system.web>
Which information please?
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239