March 7, 2012 at 4:20 am
Jeff Moden (3/6/2012)
...That's precisely what I was going to tell Eugene... the use of sp_ExecuteSQL doesn't necessarily mean that it prevents SQL Injection when someone uses it incorrectly. If you concatenate table or column names from user input, even if you use sp_ExecuteSQL, you're opening yourself up for SQL Injection.
That's precisely what I said in my post! sp_executeSQL should be used with parameters, not as simple contcatination from input.
Ok, it may be wasn't too clear.
But the main point is: Properly used Dynamic SQL doesn't constitute a security risk 😉
At the end if the table and column's names are passed as input parameters, they can be validated against sys.tables/columns isn't it?
March 7, 2012 at 4:49 am
Yes Jeff,
I dislike to make dynamic SQL like that.
(SQL Inject prone AND no statistics AND recompilations...)
Is it possible to use the @tableName and @columnName in a safe way with sp_executeSQL?
Anyway.
If I have to use something like that in an app I can use some tricks.
A good one for this case is validate the variables with regular expressions.
March 7, 2012 at 5:23 am
jcb (3/7/2012)
...Is it possible to use the @tableName and @columnName in a safe way with sp_executeSQL?
...
Simples:
IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = @tableName )
BEGIN
RAISERROR ...
END
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE name = @columnName )
BEGIN
RAISERROR ...
END
-- build SQL
...
March 7, 2012 at 5:30 am
Thanks Eugene a nice sugestion!:w00t:
But I want to know if there any way to trick SP_ExecSQL to pass a object name as a varible do a dynamic SQL as a parameter.
March 7, 2012 at 5:50 am
jcb (3/7/2012)
Thanks Eugene a nice sugestion!:w00t:But I want to know if there any way to trick SP_ExecSQL to pass a object name as a varible do a dynamic SQL as a parameter.
What's is wrong with my suggestion?
It's not possible to do what you're asking for, but what difference would it make if it could be possible?
The problem with SQL Injection is happen when you concatinate non-validatable input parameters into SQL string. For example if input parameter is the search text, it's impossible to validate it, as it may well be an SQL your are trying to search (if your searchable table holds some SQL). TableName and ColumnName (or lists of them) are easely validated, therefore do not constitute the SQL injection risk.
March 7, 2012 at 5:54 am
No problem with your sugestion.
It's realy a nice one.
I just wondering if there are some trick to pass objects names as parameters to a dynamic SQL.
As fair I know its not possible so if there are any obscure method I want to learn.
March 7, 2012 at 7:25 am
This works without leaving you open to SQL injection.
DECLARE @TableName AS NVARCHAR(20) = 'work', @SQL AS NVARCHAR(MAX);
WITH p1 AS (
SELECT sqlStmt, ROW_NUMBER() OVER(ORDER BY ord, (SELECT NULL)) AS rn, col, colname
FROM (SELECT 'SELECT COUNT(*) AS [all]' AS sqlStmt, 1 AS ord, '[all]' AS col, NULL AS colname
UNION ALL
SELECT sqlStmt + ' AS ' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))), 2, QUOTENAME(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))),
colname
FROM (SELECT ', ' + CHAR(13) + CHAR(10) + 'COUNT('+name+')', name
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName) a(sqlStmt,colname)
UNION ALL
SELECT CHAR(13) + CHAR(10) + 'FROM ' + QUOTENAME(name), 3, NULL, NULL
FROM sys.tables
WHERE OBJECT_NAME(object_id) = @TableName) a),
p2(sqlStmt) AS (
SELECT 'DECLARE @SQL AS NVARCHAR(MAX);' + CHAR(13) + CHAR(10)
UNION ALL
SELECT 'SELECT @SQL = ' + CHAR(39) + 'SELECT '+ CHAR(39) +' +'+'STUFF('
UNION ALL
SELECT 'CASE WHEN '+col+' > 0 THEN '+CHAR(39)+','+CHAR(39)+' + CHAR(13) + CHAR(10) + '+CHAR(39)+colname+CHAR(39)+' ELSE '+CHAR(39)+CHAR(39)+' END +'
FROM p1
WHERE col IS NOT NULL AND col <> '[all]'
UNION ALL
SELECT CHAR(39)+CHAR(39)+',1,3,'+CHAR(39)+CHAR(39)+') + CHAR(13) + CHAR(10) + ' + CHAR(39) + 'FROM ' +
QUOTENAME(name) + CHAR(39) + CHAR(13) + CHAR(10) + 'FROM ('
FROM sys.tables
WHERE OBJECT_NAME(object_id) = @TableName
UNION ALL
SELECT sqlStmt
FROM p1
UNION ALL
SELECT ')a;'
UNION ALL
SELECT CHAR(13)+CHAR(10) + 'EXECUTE sp_executesql @SQL;')
SELECT @SQL = COALESCE(@SQL,'') + sqlStmt
FROM p2;
EXECUTE sp_executesql @SQL;
March 7, 2012 at 7:37 am
Gentlemen, looks like you have a lot of free time...
Can some one come up with the reasonable idea for doing it in the first place?
I do not beleive it's a good design at all! Recordsets are already dynamic enough to contain variable number of rows. There will be hard to find the reason why would you want to have it with variant number of columns.
Don't try to tell me that the Object's in OOD may be implemented to have optional properties - will not play for SQL... :hehe:
March 7, 2012 at 7:47 am
Eugene Elutin (3/7/2012)
Gentlemen, looks like you have a lot of free time...Can some one come up with the reasonable idea for doing it in the first place?
I do not beleive it's a good design at all! Recordsets are already dynamic enough to contain variable number of rows. There will be hard to find the reason why would you want to have it with variant number of columns.
Don't try to tell me that the Object's in OOD may be implemented to have optional properties - will not play for SQL... :hehe:
In my business, there is no reason for doing this.
In the OPs? I wouldn't know, I'm not employed by his company. The original question was reasonably interesting and the dynamic SQL for any table was an entertaining intellectual challenge.
March 7, 2012 at 7:59 am
Cadavre (3/7/2012)
... The original question was reasonably interesting and the dynamic SQL for any table was an entertaining intellectual challenge.
Have you heard about solving SUDOKU in T-SQL?
http://qa.sqlservercentral.com/scripts/67539/
I guess the next challenge will be: "Creating 3D-Animation with T-SQL" :hehe:
March 7, 2012 at 8:01 am
Eugene Elutin (3/7/2012)
Cadavre (3/7/2012)
... The original question was reasonably interesting and the dynamic SQL for any table was an entertaining intellectual challenge.
Have you heard about solving SUDOKU in T-SQL?
http://qa.sqlservercentral.com/scripts/67539/
I guess the next challenge will be: "Creating 3D-Animation with T-SQL" :hehe:
*Ahem* --> http://qa.sqlservercentral.com/Forums/FindPost1164676.aspx
March 7, 2012 at 8:03 am
Only assefam can tell but
Sometimes bizarre or non trivial requirements arrive at my desk and sometimes they are valid.
Sometimes I cannot tell they are valid but there are cases where the client/big boss dont accept a NO even if you have a good and valid point to not do it that way.
If a requirement "dont show a columns if it never got a not null value" I can:
a) Check if is feasible to drop that columns;
b) Get a list of invalid columns ahead and turns they not visible at the report (at app level);
c) Code it;
d) Yell to the client it is bad design.
Maybe I can ask Steve to make it the next QotD.
😀
March 7, 2012 at 11:20 am
jcb (3/7/2012)
Sometimes bizarre or non trivial requirements arrive at my desk and sometimes they are valid.
And sometimes they are just misunderstood.
[..]
a) Check if is feasible to drop that columns;
b) Get a list of invalid columns ahead and turns they not visible at the report (at app level);
c) Code it;
c) Yell to the client it is bad design.
[..]
I'd bet that your (b) is actually the right answer... because it was what the higher-level boss/client really wanted; s/he just expressed it incoherently.
Note that, if we explore the request directly with the user, to find this out properly, this is not the same as your second (c), "Yell to the client it is bad design"!
Which is not to say that I don't *also* perform your second (c) pretty frequently these days, after we've had a meeting of the minds to clarify the requirement. My version of your second (c) is usually as follows: "Don't tell me how to implement! That's *my* job! Yours is to explain what you want to see, not how you want to make it happen!"
It's really hard to get somebody to focus on the original business need sometimes. Once they do, you usually find out that the requirement isn't outré at all. Then you can go back and explain to the app-level developer (in this case) what needs to happen to satisfy it, which is often (and probably, in this case) laughably trivial.
One reason it's really hard: the OP may have been dealing with a PM or BA rather than the original user/requestor of the feature. Maybe the OP doesn't have access to the original person, and can't raise the question directly. Now s/he has to convince (and, usually, to coach) the PM or BA to do *their* job properly <shrug> instead of just repeating the request like a robot, without thinking it through.
>L<
March 7, 2012 at 3:00 pm
Lisa Slater Nicholls (3/7/2012)
jcb (3/7/2012)
Sometimes bizarre or non trivial requirements arrive at my desk and sometimes they are valid.And sometimes they are just misunderstood.
[..]
a) Check if is feasible to drop that columns;
b) Get a list of invalid columns ahead and turns they not visible at the report (at app level);
c) Code it;
c) Yell to the client it is bad design.
[..]
I'd bet that your (b) is actually the right answer... because it was what the higher-level boss/client really wanted; s/he just expressed it incoherently.
Note that, if we explore the request directly with the user, to find this out properly, this is not the same as your second (c), "Yell to the client it is bad design"!
Which is not to say that I don't *also* perform your second (c) pretty frequently these days, after we've had a meeting of the minds to clarify the requirement. My version of your second (c) is usually as follows: "Don't tell me how to implement! That's *my* job! Yours is to explain what you want to see, not how you want to make it happen!"
It's really hard to get somebody to focus on the original business need sometimes. Once they do, you usually find out that the requirement isn't outré at all. Then you can go back and explain to the app-level developer (in this case) what needs to happen to satisfy it, which is often (and probably, in this case) laughably trivial.
One reason it's really hard: the OP may have been dealing with a PM or BA rather than the original user/requestor of the feature. Maybe the OP doesn't have access to the original person, and can't raise the question directly. Now s/he has to convince (and, usually, to coach) the PM or BA to do *their* job properly <shrug> instead of just repeating the request like a robot, without thinking it through.
>L<
That is called "broken phone" game played by kids in Russia.
March 7, 2012 at 3:28 pm
Eugene Elutin (3/7/2012)
That is called "broken phone" game played by kids in Russia.
And "Chinese whispers" in at least some parts of New Zealand, I have no idea why.
Personally it always makes me think of A A Milne. I can't tell you how many times I have hummed to myself "The king asked the queen and the queen asked the dairymaid..." when given an assignment third-hand. Ref: http://www.poemhunter.com/poem/the-king-s-breakfast/ Not exact analogy, but still.
FWIW: the degradation of signal doesn't even take *one* degree of separation. It only takes one person who doesn't like to think hard.
>L<
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply