2 Questions actually:
I know i must use Stored Procedures as much as Possible, but i would like to know the following please.
A: Can i get a SQL Injection attack from a SELECT statement such as (Select * from MyTable) ?
B: Also, can i get a SQL Injection attack when I use the SQLDataSource in ASP.NET?
You can get an SQL injection attack anytime that you are not using parameterized queries, for the most part.
If your example,
SELECT * from MyTable
there isn't any user-inputted data, so that should be fine. However, something like:
SELECT * from MyTable WHERE name='x'
(x being a parameter) then there's a chance that someone injects some SQL into their name.
B: ASP.NET uses parameterized queries because it builds the query based on the parameters that you provide programmatically.
if you are not using parameterized queries but are concatenating you can get sql injection for any kind of SELECT, UPDATE, DELETE or INSERT statement
To answer your questions.
A: Yes, you can get an SQL Injection attack from any query that takes parameters (even calling stored procedures if you are not using the provided methods by your platform and doing it via SQL calls).
I was asked to provide an example of how an injection can be made even by using stored procedure. I've seen applications developed that do use stored procedures, but in this way:
// C# - DON'T DO THIS!
String regionName = assignedSomewhereElse();
SQLCommand sqlCmd = DatabaseConnection.CreateCommand();
SQLCommand sqlCmd.CommandText =
String.Format("EXECUTE sp_InsertNewRegion '{0}'", regionName);
sqlCmd.ExecuteNonQuery();
Obviously, this is not the way to call a stored procedure. You should use your platform's abstractions or parametrized queries.
B: SQLDataSource is an abstraction layer for your database. It will create the SQL queries for you and automatically sanitize them in order to prevent injection.
In order to avoid injection, either:
SQL injection requires the SQL string to be combined with some user-controlled parameters, so if the Select statement is constant, it is immune to injections. On the other hand, if you're adding a "WHERE user_id =" + userIdString, then injection is possible.
Avoiding injections doesn't require stored procedures, and you shouldn't count on sanitizing your inputs. Instead, just bind to the parameters instead of manipulating strings.
Take a look at: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx
Injection hacks occur when you give the user the ability to manipulate the query, and with the parametrized queries most (if not all) threats are neutralized as special characters are escaped to make only the query you intended executable
Example: Search Box: [ ] [ GO ] --> select * from myTable where keywords like '%$searchTerm%'
Then the hacker inserts a '; to terminate the query and can write any other query they want.
Any time you allow a user to give input data into a dynamic sql query you are at risk of injection. And a sqldatasource does not protect you from injection. Inserts,deletes, drops,etc will still occur.
In response to your comment - 'what must i do?'
For starters you can validate the text boxes or whatever control will be used to allow input. If you're looking for a number make sure that they only put in numbers, if they are inputing a word make sure no punctuation is available. Take out characters like -- and ' unless it is absolutely needed. You can do all of this with ajax and/or javascript. Also,Here is an interesting Article on protection from injection. Also parameterized queries are a great option
Hi guys.
Could any of you take a look at my post to see if I am a victim of a select statement injection?
http://stackoverflow.com/questions/1373813/possible-injection-from-date-string-select-query
Many thanks