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

Is this SQL query possible?

Using Access2000, VB6

I'am not that good when it comes to SQL querys, just knowing the basics
and a little bit more.

Is this SQL query possible?
Summarizing all CustAmount, CustValue from the articles.
That belongs to a customer and then grouping them by week

3 tables
tblCustomer
tblLinkedCustomer
tblData

'tblCustomer
ID--Customer
1--K1
2--K2
3--K77

'tblLinkedCustomer
ID--Article--ArticleID--Customer--CustomerID
1--112233--3----K1----1
2--112233--3----K2----2
3--223311--4----K77----3
4--112233--3----K2----2
5--554466--1----K2----2
6--554466--1----K77----3

Rows 2 and 4 differ only in the ID column. Also, why does this table
have both Customer and CustomerID if that information is in tblCustomer?
Shouldn't there also be a tblArticle to hold information about articles?

'tblData
ID--Article--ArticleID--Customer--CustomerID--CustAmount--CustValue--InsertedWeek
1--112233--3----K1---1----12----120---0333
2--112233--3----K2---2----9----90---0333
3--223311--4----K77---3----4----32---0334
4--112233--3----K2---2----15----150---0334
5--554466--1----K2---2----15----225---0333
6--554466--1----K77---3----25----375---0334

Looks like tblLinkedCustomer is merely equivalent to,

select id, article, articleid, customer, customerid from tbldata

I've filled a treeview with Customer and Article
K1
-112233
K2
-112233
-554466
K77
-223311
-554466

Now to the problem, I need to fill a listview by week
Summarizing all article that belong to that customer

When I click on the first customer, into listview
Customer--CustAmount--CustValue--InsertedWeek
K1----12----120---0333

When I click on the second customer, into listview
Customer--CustAmount--CustValue--InsertedWeek
K2----24----315---0333
K2----15----150---0334

and so on customer by customer..select customer, insertedweek,
sum(custamount) as custamount, sum(custvalue)as custvalue
from tblData
group by customer, insertedweek

--then to see the detail you will have to use sometype of paramater

"select customer, insertedweek, custamount, custvalue
from tblData
where customer = '" & customer & "'"

not sure how you will are passing parameter, assuming in vb6, so you will have to dim customer as string and then set it equal to the customer that you selected so when you click on it you pass it to the details query.

Have funsql

Is there any REGEXP library for TSQL?

Hi guys,

Sounds a bit strange, however, if we could put some calculation in
stored procedure it would be quite convenient, just... where can I find
a REGEXP library for matching checking? thanks.

yours,
athos"athos" <athos.liu@.gmail.com> wrote in message
news:1130877928.278288.62290@.g44g2000cwa.googlegro ups.com...
> Hi guys,
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
> yours,
> athos

Take a look at the LIKE topic in Books Online to see if it meets your
requirements. Not regex but it does support some simple pattern matching.

--
David Portas
SQL Server MVP
--|||LIKE is not powerful enough. btw, COM is prohibited. thanks.|||athos (athos.liu@.gmail.com) writes:
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.

It does not sound strange at all. Some DB Engines have SIMILAR TO, and
this might even be in ANSI. I believe this uses some form of regexps.
I've been longing for it myself at times.

But for SQL2000 there is only LIKE which is far from whole covering.
You can use patindex or charindex for some stuff, but in essence it's
all very primitive.

In SQL 2005, there is no better support in T-SQL, but you can call a CLR
routine that uses the RegEx classes in .Net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
> routine that uses the RegEx classes in .Net.

I guess for SQL 2000, you could use a non-COM library as an "extended
procedure"?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
>> routine that uses the RegEx classes in .Net.
> I guess for SQL 2000, you could use a non-COM library as an "extended
> procedure"?

But performance would be awful and the code would be messy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> >> In SQL 2005, there is no better support in T-SQL, but you can call a
CLR
> >> routine that uses the RegEx classes in .Net.
> > I guess for SQL 2000, you could use a non-COM library as an "extended
> > procedure"?
> But performance would be awful and the code would be messy.

I've never written any extended procedures, so perhaps you could
explain why this would give awful performance?

I imagine the call could be as:

select ...
from ...
where myregexp_match(mycolumn, myexpression, myvalue)

Why would this be any slower than COM or .NET? Isn't this partly
what extended procedures were meant for?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:11mhdiekuhe26e9@.corp.supernews.com...
> > >> In SQL 2005, there is no better support in T-SQL, but you can call a
> CLR
> > >> routine that uses the RegEx classes in .Net.
> > > > I guess for SQL 2000, you could use a non-COM library as an "extended
> > > procedure"?
> > But performance would be awful and the code would be messy.
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

I'm guessing the main reason is that in SQL 2000, it executes outside of SQL
Server, which means for every call there's delay as it has to call out of
its address space. SQL 2005 CLR code executes within the same memory space
as SQL Server.

> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)

That's not really how you call extended stored procedure. But you could
encapsulate the XP in a user-defined function to get this syntax. However,
there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
(this overhead has been reduced in SQL 2005). If you then add a call to
extended stored procedure that gives you context switches and all, it's
getting really bad.

Then add to this that if you have a bug in your XP that causes an
access violation or similar, it's not only the XP that crashes. You
blow away the entire SQL Server.

> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

The CLR stuff in SQL 2005 is a lot more integrated in SQL Server and there
is far less overhead for invoking CLR. In fact, say that you have a decently
complex operation like some string manipulation that you can perform in
T-SQL, it is very likely to perform better in a CLR UDF. (But if you
start do data access from the CLR, it's a different picture.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > I've never written any extended procedures, so perhaps you could
> > explain why this would give awful performance?
> > I imagine the call could be as:
> > select ...
> > from ...
> > where myregexp_match(mycolumn, myexpression, myvalue)
> That's not really how you call extended stored procedure. But you could
> encapsulate the XP in a user-defined function to get this syntax. However,
> there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
> (this overhead has been reduced in SQL 2005). If you then add a call to
> extended stored procedure that gives you context switches and all, it's
> getting really bad.

Then when are XPs actually useful?

> Then add to this that if you have a bug in your XP that causes an
> access violation or similar, it's not only the XP that crashes. You
> blow away the entire SQL Server.

I understand this part, seems to be the case with pretty much all
extending to DB engines (unless managed or Java or whatever).|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
> Then when are XPs actually useful?

When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)

Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".

But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But it is correct that XP:s, as well as sp_OAcreate & co for calling
> OLE objects, have limited use, and something you only use for special
> cases.

Thanks for the explanation.

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Is there an issue running SQL 2000 on a 2003 R2 64 bit operating system?

I am trying to istall SQL 2000 on a new Server. The server is running 2003 R2 Standard x64 addition. When I try this I get an error message that says

file is valid but not for this machine type?

Are these just not compatible?I had SQL 2000 running under Windows 2003 R3 and it was fine. You have to force the setup NOT to check for machine type:

<cd drive/network drive>:\setup.exe /force

Before you install, run setup.exe /? and get the command line options and make sure that FORCE and DO NOT CHECK MACHINE TYPE options are available and used.

Also, check out the following link which gives information on SQL 2000 and the SP level needed to run under x64: http://www.sqlmanager.net/en/news/sql/mssql/683

Mark|||Here is another link from Microsoft concerning x64 support for SQL 2003 in SP4: http://www.microsoft.com/sql/prodinfo/previousversions/sp4.mspx

Mark