December 1, 2017 at 8:12 am
Hello All,
I have an unusual request today.
My company has a very old Access VBA application, this application uses inline sql to read, insert ect.. into our Azure DB. I am not sure what version this DB is.
This inline sql that is generated is:
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM payme ppd
INNER JOIN plans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) = 1,766.39 Group BY FirstName + ' '
+ LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)
Now the above will fail because of the coma for the amount 1,766, and Azure reports use of sql injection possibilities.
I have recommended that they put single quotes around the amount value, they want to just strip off the coma.
So without modifying this query only the amount how can I show a sql injection like drop table ect..
I have tried 1766 or 1=1 - this shows all records, but I want to show something that could be damaging.
The problems I am facing is the Group By clause, this cannot be modified.
Thanks,
KS
December 1, 2017 at 9:52 am
kss113 - Friday, December 1, 2017 8:12 AMHello All,
I have an unusual request today.
My company has a very old Access VBA application, this application uses inline sql to read, insert ect.. into our Azure DB. I am not sure what version this DB is.This inline sql that is generated is:
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM payme ppd
INNER JOIN plans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) = 1,766.39 Group BY FirstName + ' '
+ LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)Now the above will fail because of the coma for the amount 1,766, and Azure reports use of sql injection possibilities.
I have recommended that they put single quotes around the amount value, they want to just strip off the coma.
So without modifying this query only the amount how can I show a sql injection like drop table ect..
I have tried 1766 or 1=1 - this shows all records, but I want to show something that could be damaging.
The problems I am facing is the Group By clause, this cannot be modified.
Thanks,
KS
Instead of passing 1766 pass this. "1766; create table asdf(Hacked int);". This will probably through an error (or two) but you will probably still get a table named asdf.
Just because it is an aggregate query does NOT make it safe from sql injection. The fact that you demonstrated it can return any data should be disturbing enough. Anything short of fixing this huge problem is negligent on your companies behalf and the person making that decision should be slapped.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2017 at 10:01 am
Indeed, you would be much better off using parametrised SQL, or (even better) a Stored procedure to do the work here.
Considering that (it looks like) that code would be submitted as one line, you could just put a line comment (--) at the end of your injection to eliminate the rest of the query. So, instead change the 1766 to:1766; CREATE TABLE dbo.Injection (ID int); --
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 1, 2017 at 10:16 am
I'm not entirely clear on what you are trying to do?
Is 1,766.39 a variable?
Assuming it is a variable being passed in, and you have access to the vba code, you could try using IsNumeric to test the value of the variable, before executing the query?
Something like:
If IsNumeric(value) Then
"construct query and execute"
End If
Putting single quotes around the value will essentially declare it as a string, which I'm not sure would solve the problem.
December 1, 2017 at 11:30 am
DimPerson - Friday, December 1, 2017 10:16 AMI'm not entirely clear on what you are trying to do?
Is 1,766.39 a variable?Assuming it is a variable being passed in, and you have access to the vba code, you could try using IsNumeric to test the value of the variable, before executing the query?
Something like:If IsNumeric(value) Then
"construct query and execute"
End IfPutting single quotes around the value will essentially declare it as a string, which I'm not sure would solve the problem.
Hello Dim,
No I think the number 1766 comes from a text box that someone types into. But I am not the coder of this app nor do I want to be. And it's been decades since I touched VBA code, I don't even know it ISNumeric existed. 🙂
And with this being one of the companies SR. developers I do not have the privilege to modify this app in anyway, since the creator is now in control of it and also now our CRO. (Chief Risk Officeer).. Makes you think if he is qualified?
December 1, 2017 at 11:38 am
Sean Lange - Friday, December 1, 2017 9:52 AMkss113 - Friday, December 1, 2017 8:12 AMHello All,
I have an unusual request today.
My company has a very old Access VBA application, this application uses inline sql to read, insert ect.. into our Azure DB. I am not sure what version this DB is.This inline sql that is generated is:
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM payme ppd
INNER JOIN plans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) = 1,766.39 Group BY FirstName + ' '
+ LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)Now the above will fail because of the coma for the amount 1,766, and Azure reports use of sql injection possibilities.
I have recommended that they put single quotes around the amount value, they want to just strip off the coma.
So without modifying this query only the amount how can I show a sql injection like drop table ect..
I have tried 1766 or 1=1 - this shows all records, but I want to show something that could be damaging.
The problems I am facing is the Group By clause, this cannot be modified.
Thanks,
KSInstead of passing 1766 pass this. "1766; create table asdf(Hacked int);". This will probably through an error (or two) but you will probably still get a table named asdf.
Just because it is an aggregate query does NOT make it safe from sql injection. The fact that you demonstrated it can return any data should be disturbing enough. Anything short of fixing this huge problem is negligent on your companies behalf and the person making that decision should be slapped.
Hello Sean,
I have tried that but because of the Group By, it does not create the table
December 1, 2017 at 11:40 am
Thom A - Friday, December 1, 2017 10:01 AMIndeed, you would be much better off using parametrised SQL, or (even better) a Stored procedure to do the work here.Considering that (it looks like) that code would be submitted as one line, you could just put a line comment (--) at the end of your injection to eliminate the rest of the query. So, instead change the 1766 to:
1766; CREATE TABLE dbo.Injection (ID int); --
Hello Thom,
I have also tried that, but because of the Group by it is not creating the table.
NOTE: I am running the query from NOT the app but from SSMS, Does this make a difference? Also version 2016, not sure about AZURE.
December 1, 2017 at 11:42 am
kss113 - Friday, December 1, 2017 11:38 AMSean Lange - Friday, December 1, 2017 9:52 AMkss113 - Friday, December 1, 2017 8:12 AMHello All,
I have an unusual request today.
My company has a very old Access VBA application, this application uses inline sql to read, insert ect.. into our Azure DB. I am not sure what version this DB is.This inline sql that is generated is:
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM payme ppd
INNER JOIN plans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) = 1,766.39 Group BY FirstName + ' '
+ LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)Now the above will fail because of the coma for the amount 1,766, and Azure reports use of sql injection possibilities.
I have recommended that they put single quotes around the amount value, they want to just strip off the coma.
So without modifying this query only the amount how can I show a sql injection like drop table ect..
I have tried 1766 or 1=1 - this shows all records, but I want to show something that could be damaging.
The problems I am facing is the Group By clause, this cannot be modified.
Thanks,
KSInstead of passing 1766 pass this. "1766; create table asdf(Hacked int);". This will probably through an error (or two) but you will probably still get a table named asdf.
Just because it is an aggregate query does NOT make it safe from sql injection. The fact that you demonstrated it can return any data should be disturbing enough. Anything short of fixing this huge problem is negligent on your companies behalf and the person making that decision should be slapped.
Hello Sean,
I have tried that but because of the Group By, it does not create the table
NOTE: I am running the query from NOT the app but from SSMS, Does this make a difference? Also version 2016, not sure about AZURE.
December 1, 2017 at 11:49 am
Sean Lange - Friday, December 1, 2017 9:52 AMkss113 - Friday, December 1, 2017 8:12 AMHello All,
I have an unusual request today.
My company has a very old Access VBA application, this application uses inline sql to read, insert ect.. into our Azure DB. I am not sure what version this DB is.This inline sql that is generated is:
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM payme ppd
INNER JOIN plans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) = 1,766.39 Group BY FirstName + ' '
+ LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)Now the above will fail because of the coma for the amount 1,766, and Azure reports use of sql injection possibilities.
I have recommended that they put single quotes around the amount value, they want to just strip off the coma.
So without modifying this query only the amount how can I show a sql injection like drop table ect..
I have tried 1766 or 1=1 - this shows all records, but I want to show something that could be damaging.
The problems I am facing is the Group By clause, this cannot be modified.
Thanks,
KSInstead of passing 1766 pass this. "1766; create table asdf(Hacked int);". This will probably through an error (or two) but you will probably still get a table named asdf.
Just because it is an aggregate query does NOT make it safe from sql injection. The fact that you demonstrated it can return any data should be disturbing enough. Anything short of fixing this huge problem is negligent on your companies behalf and the person making that decision should be slapped.
Sorry, but because of the aggregate it still doesn't work, I have already tried it, and I tried it with what you said.
Sean Lange - Friday, December 1, 2017 9:52 AMkss113 - Friday, December 1, 2017 8:12 AMHello All,
I have an unusual request today.
My company has a very old Access VBA application, this application uses inline sql to read, insert ect.. into our Azure DB. I am not sure what version this DB is.This inline sql that is generated is:
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM payme ppd
INNER JOIN plans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) = 1,766.39 Group BY FirstName + ' '
+ LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)Now the above will fail because of the coma for the amount 1,766, and Azure reports use of sql injection possibilities.
I have recommended that they put single quotes around the amount value, they want to just strip off the coma.
So without modifying this query only the amount how can I show a sql injection like drop table ect..
I have tried 1766 or 1=1 - this shows all records, but I want to show something that could be damaging.
The problems I am facing is the Group By clause, this cannot be modified.
Thanks,
KSInstead of passing 1766 pass this. "1766; create table asdf(Hacked int);". This will probably through an error (or two) but you will probably still get a table named asdf.
Just because it is an aggregate query does NOT make it safe from sql injection. The fact that you demonstrated it can return any data should be disturbing enough. Anything short of fixing this huge problem is negligent on your companies behalf and the person making that decision should be slapped.
Sorry, but I have also tried that, and I even tried what you wrote, but because of the Group By it's not working
NOTE: I am running the query from NOT the app but from SSMS, Does this make a difference? Also version 2016, not sure about AZURE.
Also you will find funny, the writer/creator/owner/ no one can touch this code but him, is now our CRO (Chief Risk Officer). 🙂
December 4, 2017 at 1:54 am
kss113 - Friday, December 1, 2017 11:40 AMThom A - Friday, December 1, 2017 10:01 AMIndeed, you would be much better off using parametrised SQL, or (even better) a Stored procedure to do the work here.Considering that (it looks like) that code would be submitted as one line, you could just put a line comment (--) at the end of your injection to eliminate the rest of the query. So, instead change the 1766 to:
1766; CREATE TABLE dbo.Injection (ID int); --Hello Thom,
I have also tried that, but because of the Group by it is not creating the table.
NOTE: I am running the query from NOT the app but from SSMS, Does this make a difference? Also version 2016, not sure about AZURE.
This surprises me as, as I said before, the query appears to be a single line. Thus -- would comment out the rest of the query. I can't see anywhere in your original post that you are inserting line breaks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 4, 2017 at 9:19 am
What's the final code being run in SSMS? Can you show that?
December 4, 2017 at 12:49 pm
Steve Jones - SSC Editor - Monday, December 4, 2017 9:19 AMWhat's the final code being run in SSMS? Can you show that?
Sure here are 2 examples. Thanks The first one commented out will return all records even ones they should not see.
But the second one returns me aggregate errors and does not execute the command to create or delete a table ect..
-- SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> '
--+ Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM tblPaymentPlanPaymentDetails ppd
--INNER JOIN tblPaymentPlans pp ON ppd.PlanID = pp.ID WHERE Abs(Amount) =11766 or 1=1
--Group BY FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM tblPaymentPlanPaymentDetails ppd INNER JOIN tblPaymentPlans pp ON ppd.PlanID = pp.ID
WHERE Abs(Amount) =1766; CREATE TABLE dbo.Injection (ID int);
-- Group BY FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)
For the query above here is the errors.
Msg 8120, Level 16, State 1, Line 10
Column 'tblPaymentPlans.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 10
Column 'tblPaymentPlans.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 10
Column 'tblPaymentPlans.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 10
Column 'tblPaymentPlanPaymentDetails.PlanID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
-- Group BY FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)
December 4, 2017 at 12:53 pm
By commenting the GROUP BY the query no longer parses, so doesn't run.
December 4, 2017 at 1:04 pm
Lynn Pettis - Monday, December 4, 2017 12:53 PMBy commenting the GROUP BY the query no longer parses, so doesn't run.
but if I uncomment out the Group by I get error syntax near the key word 'group'
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM tblPaymentPlanPaymentDetails ppd INNER JOIN tblPaymentPlans pp ON ppd.PlanID = pp.ID
WHERE Abs(Amount) =1766; CREATE TABLE dbo.Injection (ID int); Group BY FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)
December 4, 2017 at 1:13 pm
kss113 - Monday, December 4, 2017 1:04 PMLynn Pettis - Monday, December 4, 2017 12:53 PMBy commenting the GROUP BY the query no longer parses, so doesn't run.
but if I uncomment out the Group by I get error syntax near the key word 'group'
SELECT max(CAST(AppliedDate as DATE)) as MostRecentDate,FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM tblPaymentPlanPaymentDetails ppd INNER JOIN tblPaymentPlans pp ON ppd.PlanID = pp.ID
WHERE Abs(Amount) =1766; CREATE TABLE dbo.Injection (ID int); Group BY FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) Order BY Max (AppliedDate)
SELECT MAX(CAST(AppliedDate as DATE)) as MostRecentDate, FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) as Client FROM tblPaymentPlanPaymentDetails ppd INNER JOIN tblPaymentPlans pp ON ppd.PlanID = pp.ID WHERE ABS(Amount) = 1766 GROUP BY FirstName + ' ' + LastName + ' -> ' + Status + ': ' + CAST(PlanID as NVARCHAR(20)) ORDER BY MAX (AppliedDate);
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply