It should be noted (and probably has been in the past) that stored procedure calls need to be parameterized as well. Use the appropriate Command class for the target DBMS such as SqlCommand, OracleCommand, etc. and build it with Parameters. The best and easiest way to perform data access is to use the Microsoft Enterprise Library. It takes a few minutes to learn but the payoff is immense. Reduced development time, simpler to adhere to best practices, and increased security (connection information can be encrypted) are just a couple benefits of using this free package.

I am surprised there is so much discussion of SQL injection when a simple coding technique can always be used to avoid it.

The most important point Michael makes is "be certain to replace single quotes (') with escaped single quotes ('') in string constants". So long as you do this you can safely use dynamic SQL.

This escaping should always be done to avoid the resulting SQL syntax errors that would usually occur when someone enters a single quote in a text field. Not doing this is simply sloppy coding. An alternative Michael mentions is sp_executesql.

The SQL injection scares remind me of the Y2K scares. There are potential problems, but these are easily solved by decent coding practice. So long as user input is confined to variable values by the escaping of single quotes or sp_executesql, then dynamic SQL is safe and often very convenient.

Thanks for the feedback. I had read that MD5 collisions were becoming more common, but hadn't realized that NIST had declared it unsecure. SHA-256 and SHA-512 are good alternatives for now.

The .NET code for validation can be as simple as a FOR...NEXT loop comparing your string character by character, a Regular Expression or it can take advantage of the ASP Validators. Here's a very simple example of a FOR...NEXT loop to validate a username consists of only the characters "A" - "Z" and "a" - "z", as I alluded to:

.NET has several built-in validators that can be used also to validate input to various degrees; I assume you're talking about the RequiredFieldValidator in your post. To use, just drag the RequiredFieldValidator onto the form next to the Password text box, and change the properties as follows:

1. Set .ControlToValidate to Password.2. Set the .Text property to a descriptive message, such as "Password Required".

T-SQL validation could also be performed using a loop, as alluded to. Here's a UDF that can be called from within a SQL SP:

Keep in mind though that even though in the example I gave all data is passed as VARCHAR's which need to be quoted, not all data passed via SQL Statements will be quoted. For instance:

SELECT * FROM Table1 WHERE IntegerID = 19823

If this were dynamically constructed, like this:

SqlString = "SELECT * FROM Table1 WHERE IntegerID = " & InputID.Text

The user can inject SQL commands with no regard for quotes. The safest route is to always use .NET parameterized queries and sp_executesql.

But probably an even bigger problem that I've seen is DBA's and developers giving the ASPNET user (and other users) far too many rights on the server and in the databases themselves. It's often a lot easier just to grant a user SA rights than to figure out exactly what rights they actually need.

To avoid SQL injection: always validate user input, use parameterized queries and sp_executesql, and limit the permissions of the ASPNET user on the server and within the database.

There are many ways to do these things, and you should choose the method that works best for you. I've posted a couple of code samples above in VB and T-SQL.

In addition, limit the amount of data sent across the wire and hash or encrypt sensitive data on the wire, and in your database, when possible. Again there are many methods of doing this, including AES Encryption, SHA-256/SHA-512 hashing, etc.

Thank you for the article. It is eye opening for me. Our company user name first letter of the first name and last name + one random number they generate. so I think the validation should be just special character. Also I do not understand why you said replace single quota with double quota.

SELECT * FROM [Login] where [User] = "test";

It will not returen any data. It have Invalid column name 'test' error.