January 28, 2010 at 6:33 am
My sincere apologies to everybody for posting code in The Thread.
It will never happen again.:-D
-- Gianluca Sartori
January 28, 2010 at 6:35 am
Gianluca Sartori (1/28/2010)
My sincere apologies to everybody for posting code in The Thread.It will never happen again.:-D
'; DROP DATABASE SSC;
:blink:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 28, 2010 at 6:36 am
Gianluca Sartori (1/28/2010)
My sincere apologies to everybody for posting code in The Thread.It will never happen again.:-D
The more code we post in the Thread, the sooner it achieves consciousness and launches it's attack against humanity. We just need to exercise caution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2010 at 6:37 am
jcrawf02 (1/28/2010)
... that I'm still just directly concatenating the user input into my SQL.
No you're not. There's no concatenation anywhere in that piece of code. Concatenation means combining a string and a parameter/variable, like this.
SET @MyString = 'Select * FROM SomeTable Where MyCol=' + @myParam
That's concatenation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2010 at 7:11 am
GilaMonster (1/28/2010)
jcrawf02 (1/28/2010)
... that I'm still just directly concatenating the user input into my SQL.No you're not. There's no concatenation anywhere in that piece of code. Concatenation means combining a string and a parameter/variable, like this.
SET @MyString = 'Select * FROM SomeTable Where MyCol=' + @myParam
That's concatenation.
See Grant, I'm not helping the Thread gain consciousness, I'm actually making it DUMBER! :hehe:
Thanks all. Have to say, that makes it even harder to understand why SQL Injection is such a problem.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
January 28, 2010 at 7:15 am
Paul White (1/27/2010)
CirquedeSQLeil (1/27/2010)
I need a couple of volunteers if any are willing and have some spare time. I am wrapping up an article and would like somebody else to review it if possible.If it covers a subject I'm familiar with, I'd be happy to take a look.
Same here.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 28, 2010 at 7:21 am
http://www.sqlservercentral.com/Forums/Topic854826-23-1.aspx
314 columns?!?!?!?!?!?!? One million records??????
And he's wondering why he's getting out of memory errors???
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
January 28, 2010 at 7:35 am
jcrawf02 (1/28/2010)
GilaMonster (1/28/2010)
jcrawf02 (1/28/2010)
... that I'm still just directly concatenating the user input into my SQL.No you're not. There's no concatenation anywhere in that piece of code. Concatenation means combining a string and a parameter/variable, like this.
SET @MyString = 'Select * FROM SomeTable Where MyCol=' + @myParam
That's concatenation.
See Grant, I'm not helping the Thread gain consciousness, I'm actually making it DUMBER! :hehe:
Thanks all. Have to say, that makes it even harder to understand why SQL Injection is such a problem.
It's a problem because people are lazy and don't use parameters, but completely build SQL on the fly in the application. That's the one area where stuff like Linq to SQL, Entity Framework, and, I think, NHibernate are better, they keep people from building SQL strings in the application and they use parameters.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 28, 2010 at 7:35 am
jcrawf02 (1/28/2010)
Have to say, that makes it even harder to understand why SQL Injection is such a problem.
SQL injection happens when a user can inject a value that they pass in such a way that the SQL engine treats it as 'executable', not as a value.
DECLARE @SomeParam VARCHAR(100) = 'abc'''; drop Database VeryImportant'
SELECT * FROM SomeTable WHERE SomeColumn = @SomeParam
The parameter is not executable here. It's a value that the column SomeColumn will be compared against. I'm saying here, SQL, please check through the table SomeTable and get me all the rows that have the exact literal value "abc'; drop Database VeryImportant"
It's probably not likely that any rows will match.
Now, the other possibility
DECLARE @SomeParam VARCHAR(100) = 'abc'''; drop Database VeryImportant'
DECLARE @sSQL varchar(500)
SET @sSQL = 'SELECT * FROM SomeTable WHERE SomeColumn = ''' + @SomeParam
EXEC (@sSQL)
In this case, the value that was passed in is being included within the statement that will be executed. Here I'm saying to SQL please take the value that's in the variable @sSQL, concatenate it to the value in the variable @someParam and then execute the resulting statement.
When it does concatenate the two statements, the result (with the string escaping cleaned up) is
SELECT * FROM SomeTable WHERE SomeColumn = 'abc'; Drop Database VeryImportant.
That's two commands. SQL will then execute those two commands one by one. It will first go through the table SomeTable and get me all the rows where the SomeColumn column has the value 'abc' and then it will drop the very important database.
Dynamic SQL's not the most common place for SQL injection though (but it can happen)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2010 at 7:44 am
You can end up with injection attacks via sp_executeSQL if you build an SQL string and then execute that, same as Exec.
For example, if you want to pass in a table name for the From clause, or a set of columns for the Where clause, and then build the query by concatenating strings together. Do that, and sp_executeSQL is no safer than Exec.
There are ways to handle that. For example, if you're passing in object names, verify that they are valid objects.
e.g.:
declare @Table sysname = 'MyTable;drop database VeryImportant;--';
if not exists (select * from sys.tables where name = @Table) raiserror('Table not found. Possible injection attack.', 16, 1);
You can do the same with lists of columns, etc. Do that before you build the executable string.
Wrapping table and column names in square brackets can help too, of course, but checking against actual object names can also prevent typos and give you a chance to handle it with a Try Catch block, instead of getting a compilation error that will often throw code onto the user's screen.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2010 at 7:45 am
This actually sort of goes along with the SQL Injection theme and the original theme of the thread. Several Threadheads have already chimed in. It's the thread where the person wants to block users from using SSMS\Query Analyzer, but the apps are using sa to connect.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 28, 2010 at 7:53 am
FYI, you're all invited to come to Memphis in April to continue this discussion on SQL Injection.
We normally meet on the 2nd Thursday of the month.
😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 28, 2010 at 8:09 am
Alvin Ramard (1/28/2010)
FYI, you're all invited to come to Memphis in April to continue this discussion on SQL Injection.We normally meet on the 2nd Thursday of the month.
😀
Thanks Alvin, it's not exactly next door for me, but thank you for the invitation.:-)
Will you come to Venice in turn?
-- Gianluca Sartori
January 28, 2010 at 8:14 am
Gianluca Sartori (1/28/2010)
Alvin Ramard (1/28/2010)
FYI, you're all invited to come to Memphis in April to continue this discussion on SQL Injection.We normally meet on the 2nd Thursday of the month.
😀
Thanks Alvin, it's not exactly next door for me, but thank you for the invitation.:-)
Will you come to Venice in turn?
I understand your comment. No, I'm not ready to go to Venice.
When I wrote the invitation, I almost added a comment about not paying for Gail's travel expenses.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 28, 2010 at 8:16 am
I think the biggest issue with SQL Injection is the front ends. It's not SQL. Even if I have a good proc, spDoSomethingCool, if I call it as
SETDim MySQL as string = "spDoSomethingCool '" + SomeVariable + "' "
Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("YourAppSettings"))
Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
And someone can input data into "SomeVariable", they could skip entering "1" and instead enter "1 ; shutdown"
The concatenation on the front end can be the issue if the developer doesn't properly send parameters through.
Viewing 15 posts - 10,891 through 10,905 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply