Is this statement vulnerable to code injection?

I found a bit of code my comapny got froma third party group that I
thought was vulnerable to code injection but I can't prove it since I
can't figure out a way to hack it. I can point the boss to sources that
say this type of programming is a Bad Thing (TM) but they want more.
Can someone help me?
The code is in asp and looks something like the following:
sLookupText = <users free text input>
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
sLookupText = Trim(sLookupText)
...
sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
Have com object execute sql.
The tricky part is that the dynamic sql is executed by the same third
party venders com objects so I don't see what happens to the query
between the time the asp hands it off and it is executed.
This is the error I get from the com obvject when I run it with the
input 1/'';select * from x;
Zero records match the criteria '1/'';select * from x;'
If I take the exact same query and run it in Query Analyzer I get the
message
Unclosed quotation mark before the character string ...
Which says I'm wrong when I think \' escapes the ' mark. So now I'm
wondering if maybe the code IS safe since the com object is sending a
different message and all the quotes are doubled in the message and all
the % are stripped.
Do the two lines
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
make it safe?
Thoughts?Michael, the only way to know what the com object sent to SQL Server is usin
g
SQL Profiler. I think the error show the com object is replacing de ' with
'', but using SQL Profiler is the easer way to know.|||sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
sLookupText = "1;-- ALTER DATABASE MSDB SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;GO;DROP DATABASE MSDB;--"
Upps... That can be something very wacky. Dynamic SQL is the hell for
security, so better have a chat with the vendor :-)
HTH, Jens Suessmeyer.|||I, uh, don't think I want to test that particular example! that drop
database part looks scary, though I'm guessing that rollback immediate
does something to undo it?
Anyway, from what I can tell, with your intput the query will be
coverted into.
select <parameter list> from table where <some field> like '1;-- ALTER
DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;GO;DROP DATABASE
MSDB;--%' order by <order clause>
As far as I can tell you never escaped out of the single quotes! Why do
you think any sql is injected?|||Michael wrote:
> I found a bit of code my comapny got froma third party group that I
> thought was vulnerable to code injection but I can't prove it since I
> can't figure out a way to hack it. I can point the boss to sources
> that say this type of programming is a Bad Thing (TM) but they want
> more. Can someone help me?
> The code is in asp and looks something like the following:
> sLookupText = <users free text input>
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> sLookupText = Trim(sLookupText)
> ...
> sSQL = "select <parameter list> from table where <some field> like " +
> sLookupText + "% order by <order clause>"
> Have com object execute sql.
> The tricky part is that the dynamic sql is executed by the same third
> party venders com objects so I don't see what happens to the query
> between the time the asp hands it off and it is executed.
> This is the error I get from the com obvject when I run it with the
> input 1/'';select * from x;
> Zero records match the criteria '1/'';select * from x;'
> If I take the exact same query and run it in Query Analyzer I get the
> message
> Unclosed quotation mark before the character string ...
> Which says I'm wrong when I think ' escapes the ' mark. So now I'm
> wondering if maybe the code IS safe since the com object is sending a
> different message and all the quotes are doubled in the message and
> all the % are stripped.
> Do the two lines
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> make it safe?
>
They make it safe when the hacker uses ' to close the quote. However, what
if he gets clever and uses the char function? OK you can do some
validation/replacing on that. But then, what if he switches to one of the
other techniques to be found in these articles:
http://www.nextgenss.com/papers/adv...l_injection.pdf
http://www.nextgenss.com/papers/mor...l_injection.pdf
The safest way to run the above query is by using parameters. There is no
way to inject sql if data is being passed via parameters.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Llike Bob already said, he could use the CHAR function.
BTW, Rollback won=B4t do any undo of the ALTER DATABASE rather than
rolling back all transaction which are currently running.
HTH, jens Suessmeyer.|||Well, I found another section of the web site with a less protected
query. I was able to convert the query (where the stuff in <> is either
table specific information not relevant to the question or user
input)...
select <columnlist> from <table> where <column> like '<users input>%'
order by <columnlist>
to
select <columnlist> from <table> where <column> like '%'; update
<table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
by <columnlist>
by entering
%'; update <table2> set <column> = 1 where <pk column> = <pk
value>;commit;--
as the input. Nice, huh! But when I checked the database nothing
happened. When I went to the DBA he used Embarcadero DBArtisan
(whatever that is) to verify that the test was sent as expected. It
was, but no injection attack worked! Any idea why? Does sqlserver allow
multiple commands on a single line? I seem to recall on my Internet
travels that one guru said that it didn't.
I really don't like seeing a dynamic query being constructed like this
but I can't seem to prove it is a problem :( I feel it is WRONG in my
bones, but I need proof to leverage a change.|||Can anyone explain why the injection attack did not work?|||Can you post a repro, so we have something to test?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michael" <miteke@.gmail.com> wrote in message
news:1143732749.601504.212240@.e56g2000cwe.googlegroups.com...
> Can anyone explain why the injection attack did not work?
>|||On 29 Mar 2006 11:53:30 -0800, Michael wrote:

>Well, I found another section of the web site with a less protected
>query. I was able to convert the query (where the stuff in <> is either
>table specific information not relevant to the question or user
>input)...
>select <columnlist> from <table> where <column> like '<users input>%'
>order by <columnlist>
>to
>select <columnlist> from <table> where <column> like '%'; update
><table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
>by <columnlist>
>by entering
>%'; update <table2> set <column> = 1 where <pk column> = <pk
>value>;commit;--
>as the input. Nice, huh! But when I checked the database nothing
>happened. When I went to the DBA he used Embarcadero DBArtisan
>(whatever that is) to verify that the test was sent as expected. It
>was, but no injection attack worked! Any idea why? Does sqlserver allow
>multiple commands on a single line? I seem to recall on my Internet
>travels that one guru said that it didn't.
Hi Michael,
SQL Server definitely allows multiple commands on one line.
Have you checked that the command that was eventually actually sent to
the server for execution was exactly as above? I'd recommend you to
insert a PRINT statement in the application just before the SQL gets
sent to the server. Or, if you can't touch the application, set up
Profiler to run a trace. A smart parser that removes quotes, reserverd
words, punctuation marks and such would have prevented your attempt to
inject SQL.
If you have verified that this is the actual code that ran, then you can
be 100% sure that SQL Server tried to do the update. It might have
failed because the SQL is executed in the context of an account with
limited permissions (that would have resulted in an error - did you see
an error when you tested it? Maybe the app has intercepted the error
message? Again, running Profiler might reveal more info). Another reason
why this update might fail is an AFTER trigger initiating a ROLLBACK, or
an INSTEAD OF trigger simply disregarding your change; in those cases,
you don't even get an error message.
Hugo Kornelis, SQL Server MVP