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

Question

Post reply

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

brandon 94807

SSC Veteran

Points: 280
More actions
June 7, 2021 at 4:55 pm
Go to Answer
#3893049
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;
ELSE
But that is dumb. Do I have to do this on every varchar?
brandon 94807

SSC Veteran

Points: 280
More actions
June 7, 2021 at 5:24 pm
Answer
#3893060

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).
August 24, 2021 at 12:29 pm

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 1

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.

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

brandon 94807 SSC Veteran Points: 280 More actions · Answer 2

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

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)
AS
BEGIN
EXEC (@intput)
END
GO
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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

brandon 94807 SSC Veteran Points: 280 More actions · Answer 4

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.