hi i would like to know if the following code can be exploited with sql injection and if so how?
DataTable dt = new DataTable();
OleDbDataAdapter daCAT = new OleDbDataAdapter();
_connection.Open();
OleDbCommand selectCMD = new OleDbCommand("SELECT * FROM Foo WHERE Bar='" + userStuff.Replace("'", "''") + "';", _connection);
i will test it, so only reply something that works not something that might work on a full moon on a specific day.
daCAT.SelectCommand = selectCMD;
daCAT.Fill(dt);
-
Follow
Oops, something didn't work.
Sign In to be begin to follow this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.Getting "follow" information
Start following this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.Removing your "follow"
Setting up your "follow"
Did you know you can
sign up for email notifications?
-
-
blowdart
Peek-a-boo
So what happens when bar is surname, and someone wants to search on O'Reilly?
What's wrong with using a parameterised query? -
the result is retrieved correctly. there's nothing wrong with using parameters but the above code isnt vulnerable to sql injection and people tend to think that it isnt so.
-
Maurits
AKA Matthew van Eerde
That code is absolutely injection-proof.
-
Maurits wrote:That code is absolutely injection-proof.
That code looks pretty vulnerable to me.
I don't see buffer size checking and no input validation to prevent a "and 1=1" scenario.
Use an ADO parameter and a stored proc everytime you pass user provided input to a SQL query.
While you're at it get yourself a copy of the Microsoft Developer Security Resource Kit DVD. http://msdn.microsoft.com/security/securityreskit/default.aspx
Enjoy. -
Maurits
AKA Matthew van Eerde
Mark Brown wrote:
Maurits wrote:That code is absolutely injection-proof.
That code looks pretty vulnerable to me.
I don't see buffer size checking and no input validation to prevent a "and 1=1" scenario.
Really...
Please, if it's not too much trouble, provide a value for userStuff that would break a buffer or create an "and 1=1" scenario.
-
JPrime
Manny Calavera
Edited: saw the replace("'", "''")...gotta think some more.
-
no full moon coming soon...
-
Maurits
AKA Matthew van Eerde
caroço wrote:no full moon coming soon...
My reasoning is as follows:
A SQL string can hold arbitrary data if it is suitably encoded.
To suitably encode a string in SQL, you double-up literal apostrophes.
You double up literal apostrophes.
Therefore, your SQL string can hold arbitrary data.
Good encoding trumps injection any day of the week.
Similarly, it's possible to pass arbitrary string data to client-side javascript by carefully encoding javascript-sensitive characters like ", newlines, and \. Something like:
var t = "<% = t.Replace("\\", @"\\").Replace("\n", @"\n").Replace("\t", @"\t").Replace("\"", "\\\"") %>";
(Disclaimer... there's probably a better way to do that.)
-
kriskdf
I really do work at MS.
If you write code like this you are begging for trouble.
Secure software starts with a secure process. If you use a process that has proven secure coding guidlines, you will have a much higher probability of not making security mistakes.
To put this another way, this particular function might be fine as is. If you were looking for a job and told me you could write secure code and showed this to me, you would not be hired. There is no valid excuse for not adding about one or two lines of code to make this parameterized if it were for a real world application (not to mention the other steps you should take to secure a database application). -
no matter what you say there's no way in hell you can inject code in the above code and the reason i posted this specific way of using sql is because people look at it and assume its vulnerable just because it's not using parameters and tell you to go read some book and whatnot but if they really understood how sql injection works they could easilly see that its secure.
i'm not posting this as demo of coding skills because its pretty much the simplest code i could think of, that none of you can exploit but just to break the assumption that anything but parameter querys can be exploited. -
blowdart
Peek-a-boo
caroço wrote:
i'm not posting this as demo of coding skills because its pretty much the simplest code i could think of, that none of you can exploit but just to break the assumption that anything but parameter querys can be exploited.
Actually if memory serves there is a limit in the length of a command string. 8000 characters maybe? Damned if I can find a reference. So depending on the behaviour exhibited, if it halts or tries to continue that could cause some interesting things.
Your code also makes the assumption that the underlying database query language uses ' as it's only terminator. That's a reasonable assumption with MS SQL, or Access, but it may not hold true for other data sources. You were the one that chose OleDb connections and not SQL, so you shot yourself in the foot.
Parameterised SQL calls aren't the be all and end all, heck that's been admitted all over the place, even on MSDN; but escewing good practice just to try to show off is not something I'd hope to see in any code my devs produce.
-
my choice of oledb was so you wouldnt be limited to one specific sql server. you can test them all.
as for the terminator its supported on access, sql server, mysql, postgres, firebird, etc and about the string length its 30000 on textboxes and 2083 for url strings on iis.
i tested it and it still isnt vulnerable so im still wainting... -
i forgot to tell you something just so you dont waste much time looking for a specific sql string limit. the replace is always made on the userstuff string so on a maxlength query string the single quotes are always escaped or the query wont be executed.
-
blowdart
Peek-a-boo
caroço wrote:my choice of oledb was so you wouldnt be limited to one specific sql server. you can test them all.
Which is still an assumption that it's SQL underneath the connection.
Really. Are you this desperate to prove something we all already know? The fun one is the encoding of HTML to avoid CSS
-
im not desperate to prove anything you are the ones who seem desperately to prove me wrong.
i actually prefer using parameter querys vs inline sql because it makes the code cleaner and it gives you a a safe type check similar to generics wich i love btw. but i just cant stand people that take assumptions as a fact. so i call this myth busted. -
blowdart
Peek-a-boo
caroço wrote:im not desperate to prove anything you are the ones who seem desperately to prove me wrong.
so i call this myth busted.
So you created a "myth" and then busted it. You must be so proud.
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.