SQL Injection

[riteshjain1982]

Is SQL Injection is possible even after replacing all single quote i.e ' from the user input with two single quote i.e '' ? .If so can you give me any example.

[Hack]

Have a look at this Wikipedia article.

[riteshjain1982]

thankx buddy for link.......as of now i am using stored procedure but i was looking for possibility of sql injection when all single quote ' from input are replaced with 2 single quote '' ......... or this simply block all sql injection

[techgnome]

If you are using stored procedures, then you should be using parameters.... and not stringing things along.... then the quote problem isn't an issue.

-tg

[riteshjain1982]

Agreed Stored procedure is the Best solution but i was just going through SQL injection article and thought wont it be a simple and full proof solution just to replaced ' with ''......

[Hack]

But, if you are using parametized queries in confjustion with your stored procedures

then the quote problem isn't an issue.

[riteshjain1982]

sorry for my bad English/grammar .........If we leave apart Stored Procedure and simply use plain SQL query like

"select * from Table  where Name ='" & strName.Replace("'","''")

So In this case how attacker can use SQL Injection

[Hack]

It doesn't matter. Remember what the article said

It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

What you are doing is embedding one language (SQL) inside of another language (VB6), and that can open the program up to SQL Injection.

[riteshjain1982]

well here thing is that i saw this approached in the code written by my friend tough he did just to make sure that sql dont throw exception when input has ' in it.Not Sure but seems accidentally he also prevented sql injection.I want to know is there is any loophole in this approach so i can prove him with example that this method still has bug

[Hack]

Not Sure but seems accidentally he also prevented sql injection.

If he is using In-Line SQL, then he is open to potential SQL Injection.

[szlamany]

But isn't the whole point of SQL injection that this:

"select * from Table where Name ='" & strName.Replace("'","''")

If strName=xyz

becomes

select * from Table where Name ='xyz'

But if strName = xyz';Delete * From Table;Select 'xyz

becomes

select * from Table where Name ='xyz';Delete * From Table;Select 'xyz'

What does that have to do with ' quotes??

[techgnome]

technically injection has nothing to do with ticks & quotes.... it has to do with how the query is built. What the replace will "fix" (which I don't like becauise it manipulates the data) is cases where strName = O'Baninon.... if not properly escaped, your sql could end up looking like this:

SELECT * FROM tblUsers WHERE Name = 'O'Baninon' .. causing an error..... but szlamany is right.... it won't prevent some one from putting O';DELETE FROM tblUsers; SELECT ' into strNAme..... this would do three things... 1) Select form the table where name is "O"... then delete all users.... then return an empty string.

-tg

[riteshjain1982]

But isn't the whole point of SQL injection that this:

"select * from Table where Name ='" & strName.Replace("'","''")

If strName=xyz

becomes

select * from Table where Name ='xyz'

But if strName = xyz';Delete * From Table;Select 'xyz

becomes

select * from Table where Name ='xyz';Delete * From Table;Select 'xyz'

What does that have to do with ' quotes??

No it will become

select * from Table where Name ='xyz'';Delete * From Table;Select ''xyz'

Becasue all ' are replaced with '' in strName.Replace("'","''")

and this wont going to cause any problme when executed....

[riteshjain1982]

sorry for wrong quey above in my 4th post it should be

"select * from Table  where Name ='" & strName.Replace("'","''")  & "'"

I personally feel Irrespective of the user input attacker wont be succeeded in SQL injection

[FunkyDexter]

All injection techniques rely on the ability to close off a command and open a new one in the variable being substituted. The more I think about it, the more I think that's impossible if you've done the replace and if you've included the outer most wrapping quotes in the string your building.

You would still be vulnerable to the "Incorrect Type Handling" in the google page Hack linked to because it doesn't rely on there being any single quotes in the variable being substituted. However, you're only vulnerable to that if the data your expecting is numeric so you don't include the wrapping quotes in the string your building.

I've been wracking my brain for abut half an hour now to come up with an example where you would be vulnerable if the data you're expecting is a string (because I instinctively believe the vulnerability is still there somewhere) and I can't come up with a single one. Because the replace replaces single quotes with a pair, no matter how the single quotes are arranged in the variable you're substituting, you will always end up with pairs of quotes by the time the DBMS comes to process it and that prevents you from closing a command and opening another erroneously. You will always end up with an extra quote on the statement you're closing. That will either be interpreted as an escaped quote (in which case the subsequent command will simlpy be interpretted as a continuation of the original string) or it'll produce something that's syntactically incorrect. You could cause a crash, but you could never produce sql that was syntactically correct and closes off command and opens another while retaining a valid syntax.

Bear in mind, I've only really considered the impact on selects here. You might be able to do more damage in an update or insert but even then I'm not sure. I think you'd just end up with data that looked a bit like sql in a field in the DB. It might look a bit wierd but shouldn't allow anyone to any real damage.

edit> Szlamany's exmpla actually gives a good example of why it can't result in damage. As Riteshjain points out, it will actually be submitted to sql server as:-
select * from Table where Name ='xyz'';Delete * From Table;Select ''xyz'
Sql server will then interpret the extra quotes as escaped single quotes and will try and select from table any record where Name is:-
xyz';Delete * From Table;Select 'xyz:-
I'm willing to bet it won't find a record and it certainly won't carry out the delete.
If the extra select (and therefore the second extra quote) hadn't been included then what would get sent to sqlserver would be
select * from Table where Name ='xyz'';Delete * From Table
That would be syntactically incorrect so would cause a crash, but the delet still wouldn't happen

[riteshjain1982]

thanks FunkyDexter for your views.the sample query i gave was from Login code itself to verify user ..........some where inside i am also feeling this can be vulnerable but unfortunately can't able to build a valid query string which can cause any damage to DB or other object

[techgnome]

And out there.... somewhere..... a DBA screams in the night....

-tg

[FunkyDexter]

And out there.... somewhere..... a DBA screams in the night....

Actually, to second TG (and just about everyone else in the thread), just because I can't think of a vulnerability doesn't mean there isn't one. Using parametised queries are a better practice and completely shut off any vulnerability so you might as well use them in preference. They're generally considered best practice, after all.

[szlamany]

I didn't get the OP's point about the ' single quote turning into a pair of ' single quotes...

I get it now

I just spent 10 minutes fooling with -- comments and /* */ comments and still can't get it to break...

But I must agree - parameterized queries are the cleanest way to handle all types of query aspects...

[si_the_geek]

There is absolutely no way I am going to go in to detail, but even after doing the Replace, injection is definitely still possible for most/all database systems.

The only truly safe way is to use parameters instead of building the statement in a string. If you are using ADO code, you would do that by using a Command object, as can be seen (and compared) in "method 3" of the FAQ article How can I add a record to a database?.

[riteshjain1982]

no doubt parameterized queries are best but here question is whether u can exploit the given scenario and as u said even after doing the Replace, injection is definitely still possible for most/all database systems. so considering SQL db and above select query just give me atleast one possible attack

[szlamany]

I think that I believe that Si does not want to give any further info.

And that makes sense.

This is a dangerous area to discuss - as SQL injection can do real damage to commercial databases.

[riteshjain1982]

I think that I believe that Si does not want to give any further info.

I think SQL injection is nothing new concept though many fact might be still unknown.........but if SI knows how one can exploit this he should share with us so one can used his example as a first test case for any applicaton........

As far as any commercial application is concern i am sure at least they are not/wont be using so poorly fabricated query

[si_the_geek]

szlamany is exactly right.. and I am not only unwilling to post details myself, but I will ban anyone else who does, as it would actually be a violation of our rules.

Giving examples would not only give dangerous information to anyone who reads this thread (which at some point will almost certainly include people with malicious intentions), but it wouldn't actually do much good anyway.

I don't know every potential exploit for every database system (and of course it can also vary by version!), so at best I could only show you a couple of examples. While that would prove to you that other holes exist, I suspect you would be inclined to 'fix' those specific issues, and ignore the others that exist - which would only give you a false sense of security, and only work for one database system.


You already know the solution to protecting your system, and you already have a test case, so why the urge to find out the methods of exploiting other systems?

[riteshjain1982]

well buddy that was very diplomat answer...........but atleast can you 100% confirm (simply yes or no) whether this query is venerable to attack on SQL 2K? ............

As a curious person if there is way to exploit this i will definitely try my best to find exmaple and will surely post in same post

[si_the_geek]

Yes, there is something that can be done for SQL Server 2000 (under certain conditions).

As I said before, posting an exploit is against our rules (see the Acceptable Use Policy at the bottom of all VBForums pages), so do not post something like that.

[techgnome]

Maybe it's time this thread be closed. It doesn't seem to be going anywhere of value. and I think the bottom line is that the question has been asked and answered. I don't really see it going anywhere except into a pi$$ing contest we really don't want.

riteshjain - seriously dude, drop it. You're looking for something that most sane people aren't going to help with. If you *//really//* want to pursue this line of reasoning, you may want to think about looking elsewhere.

-tg

[szlamany]

I agree with TG - let's just close this thread...

But I have to take issue with this

As far as any commercial application is concern i am sure at least they are not/wont be using so poorly fabricated query

Commercial applications are written using in-line SQL.

We don't do that here mostly for the benefits of datatyping that parameters gives you.

[riteshjain1982]

sorry friend if i broke forum rule and thanks for your view.As this forum is always a very good source of info for me and i didn't wanted to violate any rule intentionally. but i posted this topic just becs i came of across this example recently in one of my friends code ,i had read many article about SQL injection in past with example of almost all the possible cases but nowhere they have mentioned about the one what i came across.I spend my lot of time searching for same before posting here for more info.And my initial intention was only to know whether is venerable or not but as discussions moved ahead everyone tried their own way to break but couldn't but said is highly venerable so i cant prevent my self to learn how this case be done if answer is yes.