SQL Injection using Varchar(15) - sending in much longer than 15 characters???

  • Got an email this morning: We detected a potential exploitation of application code vulnerability to SQL injection. This may indicate a SQL injection attack on database 'XXXXXX'.

    Checked the audits, and found they were SUCCESFULLY executing the following statement:

    get_OrderVerified 'Candselect1fromselectcount*,concatselectconcatHAR52,HAR67,HAR117,HAR56,HAR89,HAR73,HAR103,HAR86,HAR72,HAR54,HAR72frominformation_schematableslimit0,1,floorrand0*2xfrominformation_schematablesgroupbyxaand', '94102', NULL, 'sample@email.tst'

    I to can run that code too. What is insane is that get_OrderVerified has varchar(15) as the first variable.  How is it possible to pass a varchar longer than the allowed length, and still execute the code?  I have put in a fix that says,

    IF LEN(@CustomerID) > 12 OR LEN(@email) > 50

    SELECT 'STOP You Dirty Rascal' AS error;


    But that is dumb. Do I have to do this on every varchar?

  • We're just guessing here... post the actual code for get_OrderVerified

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sorry - OK - I guess it was just concatenating the passed in values and the code could still execute with random characters as the varchar(15).

  • P.S. - If there is a way to delete a post let me know and I will. Thanks.

  • I don't have an Azure instance, but I just did a test on my SQL 2016 instance and was not able to reproduce what you were seeing.  My test:

    CREATE PROCEDURE [dbo].[test1]
    @intput VARCHAR(5)
    EXEC (@intput)
    EXEC [test1] 'SELECT 1'
    DROP PROCEDURE [dbo].[test1]

    When you run the above, you get an error that "SELEC" isn't a valid SQL command.

    Now, I am not sure if this is something specific to Azure trying to be helpful OR if there is something incorrect in your stored procedure definition, but I cannot reproduce what you are seeing.

    I even tried going a step further and copying the value from @input into a VARCHAR(MAX) variable prior to running and I get the same result - "Could not find stored procedure 'SELEC'".

    Is it possible to post your stored procedure definition and an example of how to reproduce the problem?  Also, do you have an on-premise version of SQL Server you can test it with?

    If not, it may be a bug in Azure and may be worth putting in a support case with Microsoft over it.


    I AM a little confused about your fix though.  Is CustomerID the field that is a VARCHAR(15)?  If so, shouldn't that first LEN be looking if it is greater than 15, not greater than 12?  And for the email check, you can probably get enough TSQL in 50 characters that you could do some SQL Injection on the email address.

    Personally, I don't think putting a character limit is a good way to prevent SQL injection.  My approach would be to remove all dynamic SQL and do some data sanitization on the input parameters.  For example, I suspect that a customer ID and an email address cannot (or more, should not) contain the ' character, so if it contains that return an error.  This should reduce a LOT of SQL injection that can happen on that stored procedure.  Also, if you can do some sanity checks on the input (application side or SQL side) that could help.  Like if the customer ID contains a space, return an error.  If the email contains a space return an error.  If either contain a *, return an error.  You won't capture ALL cases, but you can look at some that make sense to capture.  If either contain " sys." or "[sys]." or " dbo." or "[dbo]." (NOTE if there is a [, there is no space before the schema.  If there isn't a [, there IS a space before the schema), it is VERY likely a SQL injection attack and should give an error.  Length is unreliable as I could put in your 50 character email field:

    'OR 1=1;SELECT*FROM[sys].sysusers'

    and that returns true (due to the OR 1=1) AND then will give me all of the usernames, SID's, and a bit of other useful information.  And that above query - 34 characters long which is under your 50 limit.  Depending on how the query is written, it may give me an error, but that error MAY provide me with some useful info about what I can and cannot do to attack your system. Bit of trial and error and I may get access to more than I should be able to.

  • Brian - thank you for all that info - that is excellent!  I appreciate your time. In this case, it was me not doing my due-diligence. But, your information will come in helfpul.


Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply