SQLServerCentral / Discuss Content Posted by Michael Coles / Article Discussions / Article Discussions by Author / Updated SQL Injection / Latest Posts

RE: Updated SQL Injection

Joel Rea — Fri, 04 Apr 2008 11:26:09 GMT

[quote][b]AIM48 (3/24/2008)[/b][hr]Why not add a option to sql server to disallow multiple sql statements in one execution. (basically get rid of the ; in ad hoc queries). that would halt many of these attacks in it's tracks[/quote]Because it’s too useful for legitimate purposes. Suppose, for instance, you’re INSERTing a new record, and you also need to know the Identity value that was just generated for the new record (e.g. to pass an online registration ID# back to the customer, or to the PayPal payment). The only way to do so reliably is to request it immediately after the INSERT in the same batch of statements:[code]INSERT INTO myTable (various, fields, as, needed) VALUES ('various', 'data', 'as', 'needed'); SELECT SCOPE_IDENTITY();[/code]

RE: Updated SQL Injection

chisholmd — Mon, 24 Mar 2008 22:21:04 GMT

[quote][b]Pablo Emanuel (3/24/2008)[/b][hr]One more article that doesn't even mention the simpler way of avoiding SQL Injection using both ADO or ADO.Net. This one came closer when it suggested using sp_executesql with parameters, but it implies that the programmer would have to have invoked the procedure explicitly. Both ADO and ADO.Net have built-in mechanisms for invoking sp_executesql (and it doesn't need Enterprise Library or other components). You just need to use SQLParameters.SQLCommand cmd = new SQLCommand();cmd.CommandText = "select * from test_logins where username = @username and password = @password";cmd.Parameters.Add("@username", SqlDbType.VarChar,40);cmd.Parameters.Add("@password", SqlDbType.VarChar,40);cmd.Parameters["@username"].Value = Username.Text;cmd.Parameters["@password"].Value = Password.Text;The ADO/VB/ASP version is pretty similar. Notice that, besides avoiding SQL Injection, that approach has many other advantages, such as validating string lengths, aumtomatically converting types, being easier to maintain should a column type change, etc.It puzzles me how seldom it is mentioned in the forums and articles, and, therefore, how many programmers still concatenate parameters in their queries.[/quote]Isn't this precisely what is meant by using parametrized stored procedures? How else would you call one?(Well except using a procedure name as command text instead of statement.)something like:SQLCommand cmd = new SQLCommand();cmd.CommandText = "doLogin";cmd.Parameters.Add("@username", SqlDbType.VarChar,40);cmd.Parameters.Add("@password", SqlDbType.VarChar,40);cmd.Parameters["@username"].Value = Username.Text;cmd.Parameters["@password"].Value = Password.Text;set rs = cmd.excute------------create procedure doLogin@username varchar(40),@password varchar(40)asif exists (select 1 from test_logins where username = @username and password = @password) begin select 1 endelse begin select 0 end

RE: Updated SQL Injection

timothyawiseman — Mon, 24 Mar 2008 17:55:04 GMT

This was an awesome article that sums it up very succinctly. It was an excellent review for me and I am sending it to my team as well.

RE: Updated SQL Injection

AIM48 — Mon, 24 Mar 2008 07:42:17 GMT

Why not add a option to sql server to disallow multiple sql statements in one execution. (basically get rid of the ; in ad hoc queries). that would halt many of these attacks in it's tracks

RE: Updated SQL Injection

Pablo Emanuel — Mon, 24 Mar 2008 05:15:21 GMT

One more article that doesn't even mention the simpler way of avoiding SQL Injection using both ADO or ADO.Net. This one came closer when it suggested using sp_executesql with parameters, but it implies that the programmer would have to have invoked the procedure explicitly. Both ADO and ADO.Net have built-in mechanisms for invoking sp_executesql (and it doesn't need Enterprise Library or other components). You just need to use SQLParameters.SQLCommand cmd = new SQLCommand();cmd.CommandText = "select * from test_logins where username = @username and password = @password";cmd.Parameters.Add("@username", SqlDbType.VarChar,40);cmd.Parameters.Add("@password", SqlDbType.VarChar,40);cmd.Parameters["@username"].Value = Username.Text;cmd.Parameters["@password"].Value = Password.Text;The ADO/VB/ASP version is pretty similar. Notice that, besides avoiding SQL Injection, that approach has many other advantages, such as validating string lengths, aumtomatically converting types, being easier to maintain should a column type change, etc.It puzzles me how seldom it is mentioned in the forums and articles, and, therefore, how many programmers still concatenate parameters in their queries.

RE: Updated SQL Injection

Anipaul — Sun, 23 Mar 2008 23:19:29 GMT

This is a excellent article and well written too. Very simple and easy to understand. Also thanks to people who added more value to this by commenting.Regards:)

RE: Updated SQL Injection

JJ B — Fri, 21 Mar 2008 10:55:51 GMT

Dave: Thanks for the feedback!

RE: Updated SQL Injection

chisholmd — Fri, 21 Mar 2008 10:32:02 GMT

I think that is a very good point. From what I have read an exists query is very cheap performance wise.-least required priviliges-least required data (collected or retrieved)

RE: Updated SQL Injection

JJ B — Fri, 21 Mar 2008 10:23:20 GMT

I appreciate the article for it's nice high-level summary of issues to consider combined with easy to understand, detailed examples.I have one suggestion/question/tweak. This is not a criticism, just a suggestion. I'm responding to this point: "The Developer Is Sending Too Much Information Across The Wire". The article suggests just doing "SELECT EmployeeID" as opposed to the whole record in the table.What about just checking for existence? Why even send EmployeeID at all? Would it not be better to return say a Count() of matches? If the count is zero, the record is not in the table. If the count is 1, the record is in the table. If the count is more than 1, boy do you have problems. ;-)Sound like a good tweak? I'm thinking the answer may be "it depends". Of the top of my head: You would want to compare the performance of the two queries. If the existence query performs worse than the query in the article, then you would want to think about a) how much you care about that performance difference (smaller use apps on healthy servers may not matter), and b) how much of a security trade-off it really is (probably not making that much of a difference).It's a minor point. It just got me to thinking and I was interested in what others thought.- JJ

RE: Updated SQL Injection

Thomas S. Trias — Fri, 21 Mar 2008 10:08:57 GMT

Technically, client-side validation is NOT enough; hackers can craft direct HTTP POSTs (or even use tools like Fiddler to make it childishly simple to intercept and re-write POST information). Client side validation is a nicety for users, server-side validation is a MUST for developers.-Tom

RE: Updated SQL Injection

Steve Renker — Fri, 21 Mar 2008 08:48:45 GMT

Good on the whole, but I think the treatment of passwords was too glib. The best way (that I have heard of) to store a password like this is to generate and store a string of random characters (crypto people call this a "salt"), append the salt to the password (or otherwise combine them in a way you can repeat), and then hash the combination with an algorithm that is currently considered safe. Salts are important because hacker communities have created dictionaries of hashes of common passwords by themselves. The addition of a salt means they would have to hash and store, for each common password, every possible random salt of every possible length. They would also have to predict the exact way you combined password and salt before hashing.

RE: Updated SQL Injection

Jesse McLain — Fri, 21 Mar 2008 08:47:24 GMT

Great article! I have one more quarter-turn of the screw to add to locking down the database. When the stored proc that handles the login checks the database, instead of [code]SELECT * FROM Test_Logins WHERE UserName = 'username' AND Password = 'password'[/code]switch the qualifiers: [code]SELECT * FROM Test_Logins WHERE Password = 'password' AND UserName = 'username'[/code]This will prevent a hacker from guessing a login (which he could get from a published company directory), and using SQL injection; rather, assuming the other methods mentioned in the article/discussion forum failed to catch the injected SQL, a hacker would have to guess a password in the Test_Logins table, which is probably more difficult. This also assumes that users are required to use strong passwords (and cannot use something like "PASSWORD").

RE: Updated SQL Injection

Antares686 — Fri, 21 Mar 2008 07:43:40 GMT

[quote][b]Ian Yates (3/21/2008)[/b][hr]For user convenience a bit of javascript in the web browser can let the average joe know they've chosen a bad username without round-tripping.[/quote]Good comment here however consider from the standpoint of a hacker, they will go out of their way to get the pages code and client side scripting is an easy task to bypass. Validations for all important things should be server side even if is a duplication to reduce. Just keep that in mind is all.;)

RE: Updated SQL Injection

Ian Yates — Fri, 21 Mar 2008 07:37:08 GMT

Parameters, Parameters, Parameters.... Stored procs, or at least dynamic code properly using sp_executesql, is the best way to go. It can also insulate you from changes in the db, etc,etc - dynamic vs stored proc is an argument that's been done to death already :D Good article. Yes you can clean your inputs using regex, etc but parameters really help you avoid problems with /*, -- or quotes in your inputs anyway. However, you might as well use check constraints to limit usernames to appropriate characters and also perform such checks on your web server. For user convenience a bit of javascript in the web browser can let the average joe know they've chosen a bad username without round-tripping.

RE: Updated SQL Injection

Antares686 — Fri, 21 Mar 2008 07:25:29 GMT

[quote][b]Tatsu (9/21/2005)[/b][hr]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.[/quote]This is best under any circumstance of working with SQL strings.

RE: Updated SQL Injection

Elentar — Fri, 21 Mar 2008 07:15:56 GMT

I thing that this good practice to use parameters.Something like that:[code]string strSql = "UPDATE [TABLE] SET [A] = @A";System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(strSql,oSqlConnection);command.CommandType = CommandType.Text;command.Parameters.Add("@A", SqlDbType.string);command.Parameters["@A"].Value = strA;command.ExecuteNonQuery();[/code]is much better than:[code]string strSql = "UPDATE [TABLE] SET [A] = " + strA ;...[/code]Every strA is not part of SQL command but is treat as parameter.In practice I'm trying to keep all sql statements as procedures in DB (something like additional layer). It's better for application and for DB:).

RE: Updated SQL Injection

Pekka Heimonen — Fri, 21 Mar 2008 04:30:40 GMT

Very good article, thanks!However, I would like to point out that the example code written by this imaginary person "Ima" is potentially also prone to connection leak problem: whenever there's an error in the SQL command execution, the opened connection will leak - it'll be closed only when garbage collection executes. Well, at least that would be the case in C# - I'm not so sure about VB.NET.

RE: Updated SQL Injection

Carlo Romagnano — Fri, 21 Mar 2008 03:11:52 GMT

Double single quota!

RE: Updated SQL Injection

chisholmd — Fri, 21 Mar 2008 01:28:01 GMT

One thing that has not been mentioned is permission chaining. Each of my web apps uses its own SQL account and that account is ONLY given permission to run the stored procedures called from the app. Sometimes I'll use two accounts one each for different portions of the app. Because of permission chaining that account does not need permissions to access any tables directly.1) Encrypt sensitive data2) Server side validation (versus injection and cross site scripting)3) Paramertized stored procedures4) Reduced privilige account from web apps5) Recordset returned should only contain the minimum required fields (no select * ...)6) Frequent off server backups :)I think a point that should be drove home very firmly is that you can NOT rely soly on variable validation.I once read an excellent article describing all sorts of ways to trick single quote substitution. I couldn't find it again but this page offers at least one example.[url]http://dotnet.org.za/codingsanity/archive/2005/09/28/44998.aspx[/url]On a related note:Once and awhile I download some neat looking free web app written in PHP. As soon as I see all the dynamic SQL in the PHP code I can't bear it and have to delete it.Using paramertized stored procedures is:-more secure-more performant-more readable in codeAnyone who thinks its "faster and easier" just to toss dynamic SQL into their code should think again. Once you develop the habit you'll wonder why you did it any other way.

RE: Updated SQL Injection

markus-684303 — Fri, 21 Mar 2008 00:11:15 GMT

These functions clean the SQL and use Regular ExpressionsFor ASP/VB:[url=http://www.swingnote.com/downloads/mssqlsafe.txt]http://www.swingnote.com/downloads/mssqlsafe.txt[/url]For PHP:[url=http://www.swingnote.com/downloads/mysql_safe.txt]http://www.swingnote.com/downloads/mysql_safe.txt[/url]Markus Diersbock

RE: Updated SQL Injection

George Damien Varkey — Mon, 18 Dec 2006 00:05:00 GMT

Good Article. Every one should go through this.

RE: Updated SQL Injection

Mike C — Fri, 15 Dec 2006 13:55:00 GMT

Not implying by any means that you're stupid. I just wanted to make sure we're on the same page when we talk about "client-side validation". From a Web Designer's perspective, for instance, "client-side validation" might consist primarily of JavaScript-style validations. From a SQL Programmer's perspective, client-side is everything up to the point that the data is passed to SQL Server, which includes IIS validations (which would be "server-side" from the Web Designer's point-of-view).

The problem with using terms like "client-side" and "server-side" is that it fails to encompass the many layers and perspectives in a modern n-tier system, where the middle tiers act as both clients and servers. I should have probably used the more appropriate terms "data layer", "business logic layer", "application/user interface layer", etc. For purposes of this article, "server-side" is the data layer, "client-side" is everything that feeds the data layer.

As for SQL injection using parameterized queries, I've yet to see one work. SQL Server literally separates the data from the actual query with a parameterized query. I have heard of buffer overrun attacks that can work, but only if you're on older SQL Server service packs and you don't validate your data for length. If you have an example of a SQL injection technique that can circumvent query parameterization, I'd be very interested in testing it out!

Thanks.

RE: Updated SQL Injection

Joel Rea — Fri, 15 Dec 2006 13:12:00 GMT

Even with parameterized queries, new SQL Injection techniques can still work. My point, however, was that in general one should never rely on client-side validation alone. This includes even pages that do no data access, but use client-side validation or calculation for other purposes. Client-side validation (or calculation) is icing on the cake, and should always be backed up by server-side validation and/or calculation. It’s not just a matter of stopping SQL Injection attacks. It’s a matter of, for instance, preventing a user from overriding the calculated total on a shopping cart, and getting a whole shipment of PlayStation 3s for 1¢ at your client’s expense (and likely yours as well once said client rightly sues your @$$ into a bankruptcy black hole).

I’m well aware that IIS sits between the Web Browser and SQL Server, and acts as both Client to SQL Server and Server to the Web Browser. I’m not stupid. As for the [Ctrl]+[A] [Ctrl]+[C] thing, I normally do do that. I just forgot that time.

Thanks for mentioning proper parameter types and sizes. Yes, that does prevent truncation error messages, but if you don’t also warn the user about the proper length, or prevent said user from accidentally entering more text into a TextBox than the database field can hold by using MaxLength backed up by validation, the user might get ticked upon finding his or her nice long entry truncated without warning. Imagine if this Forum had a 255-character-per-post limit that it neglected to warn us about.

RE: Updated SQL Injection

Mike C — Fri, 15 Dec 2006 12:12:00 GMT

“Validation must be performed server-side. You may (and should also add client-side validation to speed up the user experience and save server round-trips for those users whose browsers support it, but you must always back it up with equivalent server-side validation. It’s trivially easy for hackers to bypass client-side validation, either by faking GETs or even POSTs, bypassing your Hidden and Read-Only fields, stuffing non-listed values into your drop-down menus and radio button lists (all of those can be done with a very common Firefox Extension, for instance), or even by simply turning off JavaScript!” — Much better.

I agree *if* you are not using parameterized queries, and are using the string concatenation method (very bad idea) as pointed out in the article. If, however, you are using parameterized queries, intermediate SQL Server server-side validation becomes less of an issue as you don't have to be concerned with SQL commands being injected directly into your queries via parameters, nor with semicolons, apostrophes, or double dashes wreaking havoc in your queries. Also, if your parameters are defined explicitly to be of the appropriate type and length, truncation and overrun errors from the UI are not an issue that IIS can't resolve.

Proper DRI, FK relationships, and check constraints on tables will help prevent garbage data from being entered server-side as well.

Faking GETs and POSTs is beyond the scope of the article, but client-side validation in terms of SQL Server includes IIS validation. IIS is just another "client" as far as SQL Server is concerned. When your hidden form field variables, etc., are validated on IIS before IIS submits the query to SQL Server, faking GETs, POSTs, and manipulating form fields is not an issue as far as SQL Server is concerned. Your reference to client-side JavaScript validation makes me think you are considering the client-server model like this:

---------------IIS (Server)Web Browser (Client) --------| ---------------SQL Server (Server)

When in actuality the Web Browser is not directly connecting to the SQL Server. The actual model is more like this:

Web Browser (Client) ------- IIS(Server/Client) -------- SQL Server (Server)

(Of course on SQL 2005, with HTTP Endpoints, this model changes yet again... But that's for another article).

BTW - When I type a really long post (like this one), I usually highlight the whole thing (CTRL+A) and copy it to the clipboard (CTRL+C) before submitting to keep it from goinf *poof* when I hit submit. If it does disappear, I can just paste it right back in and hit submit again

RE: Updated SQL Injection

Joel Rea — Fri, 15 Dec 2006 11:06:00 GMT

Oooooo-kaaay! I had just written up a long reply here with lots of important information, and went to HTML view to edit some of the generated code (did you know that this Forum software inserts “ ”s whenever you go back and edit already-written text in the Design view?), then clicked Preview, and voila! Bye-bye message! Empty editor box! Bad Forum software! Bad!!

Well, I’m not typing the whole thing again. I have work to do. So, you’ll just have to settle for this one bit of dangerously bad advice that I noticed in your article:

“Validation can be performed server-side, client-side, or even both!” — Bzzzzzt! Wrong answer!

“Validation must be performed server-side. You may (and should also add client-side validation to speed up the user experience and save server round-trips for those users whose browsers support it, but you must always back it up with equivalent server-side validation. It’s trivially easy for hackers to bypass client-side validation, either by faking GETs or even POSTs, bypassing your Hidden and Read-Only fields, stuffing non-listed values into your drop-down menus and radio button lists (all of those can be done with a very common Firefox Extension, for instance), or even by simply turning off JavaScript!” — Much better.

ASP.NET 2’s built-in Validators can add the appropriate client-side code for you, and the CustomValidator can allow you to code a JavaScript client-side custom validator linked to it. These will automatically be rendered to browsers known to support the appropriate level of JavaScript and either Microsoft or W3C DOM.

So, for instance, if you want to prevent anything except alphanumerics from being entered into the Login Name field, as well as limiting the number of characters (you should always do this anyway, both to prevent overflowing the database field [“Text or binary data would be truncated.” server error] and to shorten the amount of SQL code that a hacker could inject should he manage to get past all of your protections), you can do this:

User Name:<asp:TextBox runat="server" ID="tbUserName" Columns=16 MaxLength=16 /><asp:RequiredFieldValidator runat="server" ID="rfvUserName" ControlToValidate="tbUserName"   EnableClientScript="true" Text="&times;" ErrorMessage="User Name is REQUIRED!" /><asp:RegularExpressionValidator runat="server" ID="rgxvUserName" ControlToValidate="tbUserName"  ValidationExpression="^\w{4,16}$" EnableClientScript="true" Text="&times;"  ErrorMessage="User Name must be between 4 and 16 alphanumeric (or underscore) characters long." />

...

<asp:ValidationSummary runat="server" ID="vsLogin" DisplayMode="BulletList"  HeaderText="Please correct the following and try again:" EnableClientScript="true" />

Note that not a single line of VB.NET or C#.NET code is required! This is all done in ASP.NET 2 tags! Two Validators do all the work: RequiredFieldValidator to make sure that something was entered, and RegularExpressionValidator to make sure that it was within the proper length range and consisted entirely of upper or lower-case letters, digits, and underscores. No other characters (including spaces) accepted, let alone such SQL Injection-dangerous ones as single quotes, hyphens, or semicolons.

The other thing I went on at some length about before this lousy Forum ate my post was Macrodobe Dreamweaver. Suffice it to say that the ASP and ASP.NET 1.1 (DW doesn’t even support ASP.NET 2 except for hand-coding) code generated by any version of Dreamweaver is horrendously inefficient, but that if any of you used it in the past to get started with ASP or ASP.NET and you still have any sites out there that use it, you must upgrade to DW 8.0.2 and rebuild your ASP Recordsets or ASP.NET DataSets, or else replace them with proper hand-coded parametized safe and efficient code. All versions of DW prior to 8.0.2, from the original UltraDev on up through 8.0.1, produce code that has only minimal and easily bypassable protections against SQL Injection.

RE: Updated SQL Injection

Debra Hodges-389283 — Fri, 15 Dec 2006 07:40:00 GMT

Wonderful article! Thank you so much!

RE: Updated SQL Injection

Mike C — Thu, 21 Sep 2006 08:12:00 GMT

To "escape" single quotes in SQL Server, you have to double them up meaning you put two single quotes back-to-back:

SELECT * FROM [Login] where [User] = 'Frances'' test of SQL''s escaped quotes';

Notice that the escaped quotes in the example are two separate single quotes, not one "double" quotation mark.

Thanks

RE: Updated SQL Injection

Ninja's_RGR'us — Thu, 21 Sep 2006 07:41:00 GMT

No the concatenation becomes this :

SELECT * FROM [Login] where [User] = 'Ninja''s_RGR''us';

RE: Updated SQL Injection

Frances L — Thu, 21 Sep 2006 06:19:00 GMT

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.

RE: Updated SQL Injection

Mike C — Wed, 21 Sep 2005 10:57:00 GMT

Thanks for the feedback.

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.

Thanks!

RE: Updated SQL Injection

Mike C — Wed, 21 Sep 2005 10:52:00 GMT

Thanks for the feedback!

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.

Thanks!

RE: Updated SQL Injection

Mike C — Wed, 21 Sep 2005 10:47:00 GMT

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:

Function ValidUser(s As String) As Boolean Dim f As Boolean = True s = s.ToUpper() For i = 0 To s.Length - 1 If (s.Substring(i, 1) < "A" OrElse s.Substring(i, 1) > "Z") Then f = False EndIf NextEnd Function

.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:

CREATE FUNCTION dbo.udf_ValidUserName(@s VARCHAR(255))RETURNS CHAR(1)ASBEGIN DECLARE @i INTEGER SET @i = 1 DECLARE @result CHAR(1) SET @result = 'T' SET @s = UPPER(@s) WHILE @i <= LEN(@s) BEGIN IF (SUBSTRING(@s, @i, 1) < 'A' OR SUBSTRING(@s, @i, 1) > 'Z') BEGIN SET @result = 'F' END SET @i = @i + 1 END RETURN @resultEND

Thanks!

RE: Updated SQL Injection

saleem-200718 — Wed, 21 Sep 2005 08:06:00 GMT

This is a great artical and i have seen many describing the threat of SQL injection.

I would recommend to give the solution also using which we can avoid the Injection threat.

RE: Updated SQL Injection

Renato Buda-153382 — Wed, 21 Sep 2005 07:48:00 GMT

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.

RE: Updated SQL Injection

mark hutchinson — Wed, 21 Sep 2005 07:17:00 GMT

How about a .Net code example of the validation to which you allude (username character limits)?

It would been more helpful if you mentioned the use of .Net validators and gave a code example. For instance, you might include a validator that requires a password.

How about a TSQL code example of the validation to which you allude (username character limits)?

Excellent example of a reason for restricting access to only stored procs and not tables. kudos.

You might hash both the username and password field data and send it to the stored proc or at least the password field data.

If you are concerned about man-in-the-middle attacks/sniffers, you might send some special data back from the stored proc, such as the hash of username & password & datetime (fuzzy).

MD5 is no longer considered a trustworthy hash by NIST:http://www.codeproject.com/useritems/GoodbyeMD5.aspa better recommendation is SHA-256 (or stronger).

RE: Updated SQL Injection

Tatsu — Wed, 21 Sep 2005 07:04:00 GMT

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.

RE: Updated SQL Injection

N Cook — Wed, 21 Sep 2005 06:47:00 GMT

Good article. Thanks.

Updated SQL Injection

Mike C — Mon, 29 Aug 2005 21:11:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp