Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Injection - methods to block

    I have looked into the world of SQL injection attacks and tried some of these out on duplicates of live systems on my dev machine, and noticed that the only time one actually worked was on a simple userID/password form tied to a SQL Server. So I just switched the login page to read an Access db instead. On my other system, which uses a VERY common technique for me, none of the SQL Injection attackes I tried worked (using -- etc in a text field, for example). My theory is that the technique I used does somethings that foil Injection attacks:

    The Dynamic SQL Code appends a datestamp value to the field/value string compiled from the form field elements. Therefore, malicious code in the last textbox of a form is wrapped in the SQL string as the next to last field/value so it can't execute. Also, the field/values in the dynamic SQL string are automatically wrapped in (') so that the Injection code is basically treated as intended - a field/value pair, not, say, a DML command.

    Does this sound right or have I just been lucky/not testing a more malicious attack strategy?

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    If you're building the SQL statement dynamically and executing it directly, then you're vulnerable. I think you've been lucky so far...

    The best way to avoid SQL Injection is to use parameterized SQL Statements (either Stored Procedures or prepared SQL Statements that use Parameters). Also, you should clean ALL user input (i.e. Server.HtmlEncode) so no nasty code can be inserted as code - rather it will be converted to text. This will help prevent SQL Injection as well as scripting attacks. Jeff Prosise, an expert on this topic, has written an article that explains the attack and how to avoid it: http://msdn.microsoft.com/msdnmag/is.../SQLInjection/.

    Afterthought.... Haven't we discussed this before? <post:=406,732>post 406,732</post:>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    I tend to agree that I've been lucky so far, and have definately recommended moving to Stored Procs under well-formed user/roles for web-based interactions.

    I was wondering if the fact that I have appended another field/value pair in the dynamic SQL was, in fact, helping to prevent many common SQL Injection attacks, tho.

    yeah we discussed this on an earlier thread but not this specific question...

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    Can you provide a sample of the SQL and ASP code about which you're asking - just for clarity?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    The actual code is a bit long so I will attach it to this post.

    I have a method for pulling all the form field names and values from a series of hidden fields via a POST. I build the hidden fields using Replace(Request.Form(item), "'", """") to prevent trunctated values on INSERT. In practice, this also derails any SQL Injection attacks that rely on ' DML code strategies and the attempted hack is just stored in the table as a form field value. However, as you can also see, the SQL String sent to the db also adds a timestamp to the end of the SQL String so any SQL Injection code cannot be tagged onto the end of the SQL String using a form.

    One of the major advantages of my technique is easy maintenance of the code pages: you just match the form field element NAME to the db column and the code will pick up the rest.
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    It looks a lot more secure than much of my early classic ASP code! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Assuming you're locked into the current process/architecture, I would recommend implementing some additional checks on the server side before appending the value for insert. In particular, I would recommend adding the replace functionality on the server side since you're pulling the value from a hidden field (which can be modified by a user before submitting).

    Otherwise, it looks pretty solid. You might want to request a few hours from a consultant or company specializing in security to attempt to break in or hack your application. That would definitely give you a good idea of just how secure it is...

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    So... are you saying this technique, even tho it uses 'evil' dynamic SQL, is not all that bad? and, by server-side validation, do you mean something on the SQL Server? Some kind of hinky T-SQL crapola? (I don't like T-SQL much, FWIW). I've compromised to the .NET security people by using stored procs for this, which means... more work to maintain data from the form to the db, which is inconvenient. it also means I can't use this technique, unless I figure out how to dynamically create the SQL code for the INSERT to a stored proc.

    Also, to round things out, this info is being sent under SSL so I'm assuming the hacker out there is not going to be able to easily modify the SQL String 'in-flight'.

    AND, just to try and stay realistic, just how big a deal in the real-world is SQL Injections? I've never heard of an actual problem with such attacks. It seems more theoretical than real, IMHO.

    If I can get people to stop boosting their pet technology and work with what - well, works - then perhaps I may propose bringing in some outside security people who specialise in trying to hack into networks. I happen to know someone who works in such a shop...

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Injection - methods to block

    <P ID="edit" class=small>(Edited by jscher2000 on 01-Jun-06 12:50. )</P>In my view, what server-side means in this case is when your form is posted. You "cleaned up" the hidden fields before serving them back to the client, but a client-attacker will modify the hidden fields when POSTing them. You need to sanitize them again before sending them to SQL Server.

    Added:

    > AND, just to try and stay realistic, just how big a deal in the real-world is SQL Injections?

    You would think it's only a problem on the web, but internal information can be even more closely guarded (and sought). Classic example: dumping the tables with employee salary information. You won't find that attacker on the front page of the New York Times. No, that employee was quietly paid off because prosecution would be too embarrassing.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    so, implement the Replace function in the SQL String build rather than the block of hidden form fields. easy enough to do it that way...

    my new unit is doing quite a bit of work with various db files and info and i'd like to have our backend db completely isolated on a small network only our unit can access. that, of course, is a different discussion from fending off people who insist that ASP Classic is 'inherently insecure' and therefore shut down existing systems for complete re-writes and refuse to even host a simple survey form on thier web server/SQL Server machines...

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    In addition to Jefferson's excellent comments, let me point out that you should not be afraid to use stored procedures (or parameterized SQL statements). It may be something new to learn, but the payoff is huge. In a best practice situation, the Stored Procedures are no more complicated than the current SQL code you're generating - just basic CRUD (Create, Read, Update, Delete). Developers often place compliceated business logic in T-SQL, which is frankly a bad practice.

    You can easily learn how to connect ASP to a stored procedure in just a couple of hours by searching for "ASP Command parameter Stored Procedure" (or some variation therein).

    Also, I would recommend looking into a code generator utility (such as MyGeneration - which is free). With this tool, you can generate CRUD procedures for any table(s) with just a few mouse clicks! That will take much of the headache out of the work...

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    Actually I do know how to implement stored procs, and I have to disagree with you on their payoffs in terms of efficient development of forms to the db. I know this sort of thing can become a "religious issue" but, I'll go ahead and dive in...

    my coding strategy uses dynamic SQL automatically generated based on reading the DOM on the form elements to assemble a well-formed CRUD statement. The benefit is, I just have to match a form element NAME to the db table Column and make sure the column specification works with the expected data type, and that's it. Stored Procs have to list each and every column name and data type AGAIN, which is a pain. Unless i am running some process that is processor-intensive and frequent, why use a stored proc? The usual issues raised in favor of stored procs is that they are compiled or cached or something and have greater performance on re-issue. This I don't deny but it's not really applicable in many circumstances.

    The only things I can see about stored procs that makes them useful is that they can be restricted to only do what is expected (a CRUD action) and can't be 'highjacked' by a hacker to execute DML commands - I guess. However, can't one create a login profile for the the webform's connection that is just as restrictive? I've made that point more than once around here and the people who I need to work with on the db side simply REFUSE to consider anything other than complete rewrites of my systems in .NET. This has been a huge problem as they have taken high-level applications off-line and cannot, IMHO, justify doing so. In the spirit of compromise I have agreed to implement stored procs so this discussion is somewhat academic, but I'd like to know if my point of view is valid overall.

    FWIW, the production enviornment they want to implement .NET on is a webfarm our agency does not control, and the webfarm uses Java/Websphere and ASP Classic. There is no support for .NET on the webfarm, so they have a lot of work to get a server running on .NET, which means the high-level web app will be off-line indefinately...

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    <hr>Stored Procs have to list each and every column name and data type AGAIN, which is a pain.<hr>
    Steve, if I understand correctly, your philosophy is centered around avoiding the extra effort of creating the stored procedures? That's a shame - especially when you can easily use code generators to create these with just a few mouse clicks.

    Also, keep in mind that the same benefits of stored procedures can be gained (including the compiling and optimized execution plans) by using parameterized pepared sql commands, which can be initiated from your application and do not require creating stored procedures.

    Your current situation could benefit from a separate discussion focusing on implementation strategies. For the record, I agree that it's generally a good idea to plan an implementation with the goal of keeping a legacy application live through the transition.

    It sounds like you're totally against .NET for some reason that's totally unrelated to the technology itself. Keep in mind that .NET has been around for about 5 years. It's not going away and it's a much solid technology than Classic ASP. As I've said before, I wouldn't suggest that anyone should drop everything and rewrite their well-developed legacy applications. But I would suggest that any future enhancement effort be shifted to the .NET arena (especially now that 2.0 has been released).

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Injection - methods to block

    actually I have been looking at parameterized SQL as well and, like I said, I am willing to deal with stored procs in general, but I'm not sold on them as being a time-saver. Obviously, my desire to be really efficient in terms of development is not a trump, especially if one ends up making a system vulnerable or inefficient in terms of performance. those are the trump metrics.

    Also, I have no problem with .NET in and of itself, but I have enormous problems with people who refuse to work with existing function applications and refuse to explain exactly why they feel the existing applications are 'inherently insecure' and therefore must be shut down and completely re-written. This kind of attitude makes me irritable, and may in fact bleed into suspicion re. .NET boosters. Anyhow, given the deployment enviornment we must work with (Java/Websphere, no support for .NET machines) I am also keen to fix any problems with asp classic apps (which are supported) rather than pushing for a .NET solution "just because".

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Injection - methods to block

    I'm not sure about the repeating part, as I've only used parameterized queries in the humble MS Access database. The ADO to add parameters is a bit clunky but probably no worse than the code/script to build a query using the same names and values. The result is your VBA becomes extremely simple to understand. In this example, the query anticipates two criteria for a find:

    <code>'ADO module-level variables
    Private conTR As New ADODB.Connection
    Private cmdTR As New ADODB.Command
    Private rstTR As New ADODB.Recordset
    ...
    With cmdTR
    .CommandText = "QueryName"
    For intCounter = (.Parameters.Count - 1) To 0 Step -1
    'clear any earlier parameters
    .Parameters.Delete intCounter
    Next
    'PARAMETERS usrField1value Text ( 255 ), usrField2value Text ( 255 );
    .Parameters.Append cmdTR.CreateParameter("usrField1value", _
    adVarWChar, adParamInput, 12, strUserInput1)
    .Parameters.Append cmdTR.CreateParameter("usrField2value", _
    adVarWChar, adParamInput, 12, strUserInput2)
    End With
    rsType = adOpenForwardOnly
    ...
    'run query against my table
    'Create and open ADO connection
    With conTR
    If .State = adStateClosed Then
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "Data Source=" & strPathToDatabase
    End If
    End With
    'set active connection and open recordset
    cmdTR.ActiveConnection = conTR
    rstTR.Open cmdTR, , rsType, adLockReadOnly, adCmdStoredProc
    ...</code>

    Note that the ... represents vast quantities of code omitted, since the third chunk will run any of a half dozen different queries depending on the user's choices in a sequence of dialogs. I don't know why "CRUD" operations would be significantly different.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •