December 15, 2006 at 1:12 pm
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.
December 15, 2006 at 1:55 pm
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.
December 18, 2006 at 12:05 am
Good Article. Every one should go through this.
March 21, 2008 at 12:11 am
These functions clean the SQL and use Regular Expressions
For ASP/VB:
http://www.swingnote.com/downloads/mssqlsafe.txt
For PHP:
http://www.swingnote.com/downloads/mysql_safe.txt
Markus Diersbock
March 21, 2008 at 1:28 am
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 data
2) Server side validation (versus injection and cross site scripting)
3) Paramertized stored procedures
4) Reduced privilige account from web apps
5) 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.
http://dotnet.org.za/codingsanity/archive/2005/09/28/44998.aspx
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 code
Anyone 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.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 21, 2008 at 3:11 am
Double single quota!
March 21, 2008 at 4:30 am
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.
March 21, 2008 at 7:15 am
I thing that this good practice to use parameters.
Something like that:
string strSql = "UPDATE
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();
is much better than:
string strSql = "UPDATE
SET [A] = " + strA ;
...
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:).
March 21, 2008 at 7:25 am
Tatsu (9/21/2005)
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.
This is best under any circumstance of working with SQL strings.
March 21, 2008 at 7:37 am
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 😀 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.
March 21, 2008 at 7:43 am
Ian Yates (3/21/2008)
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.
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.;)
March 21, 2008 at 8:47 am
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
SELECT * FROM Test_Logins WHERE UserName = 'username' AND Password = 'password'
switch the qualifiers:
SELECT * FROM Test_Logins WHERE Password = 'password' AND UserName = 'username'
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").
March 21, 2008 at 8:48 am
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.
March 21, 2008 at 10:08 am
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
March 21, 2008 at 10:23 am
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
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply