August 11, 2010 at 6:13 pm
This gives an idea of what I need:
SELECT tblLocation.ID
FROM tblLocation
WHERE tblLocation.Route IS NOT NULL
AND tblLocation.Route <> '0-0'
AND tbllocation.Inactive = '0'
AND tblLocation.Route LIKE '1010-%'
AND tblLocation.ID NOT IN
(SELECT tblManifest.LocationID
FROM tblManifest
LEFT JOIN tblManifestDetail ON tblManifestDetail.ManifestID = tblManifest.ID
WHERE (SELECT MAX(t1.ManifestDate) FROM tblManifest t1 WHERE t1.ID = tblManifestDetail.ManifestID) >= '08/11/2008'
AND (tblManifestDetail.MatCode = '1AGA' OR tblManifestDetail.MatCode = '3AGA' OR tblManifestDetail.MatCode = '3EGA'))
This runs but returns either all or none of the rows depending if 'NOT' is used. For this route it should return just one row.
August 11, 2010 at 7:16 pm
Interesting post (and a bit quirky to see the anti-stored proc crowd banding together) ... I feel compelled to respond:
DataDog (8/11/2010)
cached query plans are not always good
If you're having issues with bad plans being cached, a better solution would be to figure out why your proc is compiling unreliably and fix the problem rather than just not having a cached plan.
if they are not reused they waste resources
True to a very small degree, but this would be outweighed by the resource cost of unnecessary recompilations.
and if they are dependent on parameters they are worse than compiling fresh
A bad plan can be horrible ... not sure if you're referring to parameter sniffing, but that would be especially true. Again, however, a better solution is to understand and fix the problem rather than making a different mistake to avoid this one.
"SQL Injection" is just a silly name for "not encoding literals properly"
I think "SQL Injection" is a sad name for "I'm an app developer who doesn't adhere to best practices because I think I'm smarter than database experts and I leave all my data wide open as a result".
create some helper functions:
public static string SqlName(string name) {return "[" + name.Replace("]", "]]") + "]";
}//method
public static string SqlString(string name) {
return "'" + name.Replace("'", "''") + "'";
}//method
and then build on those until your SQL strings are constructed mostly from functions
less chance of mistakes and "sql injection" that way
Although injection can be avoided through various means, if you're building a string in code and passing it to the SQL Server you will need a lot more code than what's listed above to safeguard your system. Also, with the string cleaning that needs to occur, it's very possible that you'll end up removing characters that you would otherwise want such as punctuation in a comment field. And there is yet another reason why it's an ill advised approach.
If you're building your string in the application and executing it against the SQL Server, your app has to be using an account that has significant permissions against the data itself. A better way to go is to use a limited SQL account that only has permissions to specific stored procs. Even a hijacked app can't do more than the procs will allow under this setup.
I don't expect anyone who is dedicated to the avoidance of stored procedures to be compelled by any of what I say, but others who don't know any better may read this forum too.
August 12, 2010 at 3:03 am
This does work after all. I was using the wrong test route.
I feel extra dumb now, but I did learn a lot here.
Thank you.
SELECT tblLocation.ID
FROM tblLocation
WHERE tblLocation.Route IS NOT NULL
AND tblLocation.Route <> '0-0'
AND tbllocation.Inactive = '0'
AND tblLocation.Route LIKE '1010-%'
AND tblLocation.ID NOT IN
(SELECT tblManifest.LocationID
FROM tblManifest
LEFT JOIN tblManifestDetail ON tblManifestDetail.ManifestID = tblManifest.ID
WHERE (SELECT MAX(t1.ManifestDate) FROM tblManifest t1 WHERE t1.ID = tblManifestDetail.ManifestID) >= '08/11/2008'
AND (tblManifestDetail.MatCode = '1AGA' OR tblManifestDetail.MatCode = '3AGA' OR tblManifestDetail.MatCode = '3EGA'))
August 12, 2010 at 3:21 am
Dennis, I'm not sure if this will work because there's nothing to test against, but give it a try. Even if it doesn't, it should give you a few ideas:
SELECT l.ID
FROM tblLocation l
WHERE l.[Route] LIKE '1010-%'
--l.Route IS NOT NULL -- redundant
--AND l.Route <> '0-0' -- redundant
AND l.Inactive = '0'
AND l.ID NOT IN
(SELECT mh.LocationID
FROM tblManifest mh
INNER JOIN tblManifestDetail md ON md.ManifestID = mh.ID
AND md.MatCode IN ('1AGA', '3AGA', '3EGA')
WHERE mh.ManifestDate >= '08/11/2008')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 12, 2010 at 3:26 am
create some helper functions:
public static string SqlName(string name) {return "[" + name.Replace("]", "]]") + "]";
}//method
public static string SqlString(string name) {
return "'" + name.Replace("'", "''") + "'";
}//method
if you're building a string in code and passing it to the SQL Server you will need a lot more code than what's listed above to safeguard your system
I have always been curious about this point - can you give an example of a user supplied string that can defeat:
string literal = "'" + formdata.Replace("'", "''") + "'";
If you're building your string in the application and executing it against the SQL Server, your app has to be using an account that has significant permissions against the data itself. A better way to go is to use a limited SQL account that only has permissions to specific stored procs. Even a hijacked app can't do more than the procs will allow under this setup.
? web app sql user has GRANT SELECT and GRANT EXECUTE only ? how is "sql injection" going to attack?
August 12, 2010 at 4:07 am
DataDog (8/12/2010)
create some helper functions:
public static string SqlName(string name) {return "[" + name.Replace("]", "]]") + "]";
}//method
public static string SqlString(string name) {
return "'" + name.Replace("'", "''") + "'";
}//method
if you're building a string in code and passing it to the SQL Server you will need a lot more code than what's listed above to safeguard your system
I have always been curious about this point - can you give an example of a user supplied string that can defeat:
string literal = "'" + formdata.Replace("'", "''") + "'";If you're building your string in the application and executing it against the SQL Server, your app has to be using an account that has significant permissions against the data itself. A better way to go is to use a limited SQL account that only has permissions to specific stored procs. Even a hijacked app can't do more than the procs will allow under this setup.
? web app sql user has GRANT SELECT and GRANT EXECUTE only ? how is "sql injection" going to attack?
1. Reagardless of possibility of defeating of your nice literal wrapping function, this way of building the dynamic SQL is definitely lays outside of best practices. At least some ORM solution should be used instead (eg. NHybernate for example).
2. The difference between GRANT SELECT and GRANT EXECUTE is huge:
While first permission allows only select from table/view, GRANT EXECUTE allows exeuting procedure which can perform SELECT and INSERT/UPDATE/DELETE if required. Now, how many application for databases (except Reporting ones) require exclusively read access and never update?
August 12, 2010 at 4:10 am
dennisv (8/12/2010)
This does work after all. I was using the wrong test route.I feel extra dumb now, but I did learn a lot here.
Thank you.
SELECT tblLocation.ID
FROM tblLocation
WHERE tblLocation.Route IS NOT NULL
AND tblLocation.Route <> '0-0'
AND tbllocation.Inactive = '0'
AND tblLocation.Route LIKE '1010-%'
AND tblLocation.ID NOT IN
(SELECT tblManifest.LocationID
FROM tblManifest
LEFT JOIN tblManifestDetail ON tblManifestDetail.ManifestID = tblManifest.ID
WHERE (SELECT MAX(t1.ManifestDate) FROM tblManifest t1 WHERE t1.ID = tblManifestDetail.ManifestID) >= '08/11/2008'
AND (tblManifestDetail.MatCode = '1AGA' OR tblManifestDetail.MatCode = '3AGA' OR tblManifestDetail.MatCode = '3EGA'))
If you can, I'd love to see an execution plan for this. I'm sure more could be done to make it run better.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 12, 2010 at 5:17 am
2. The difference between GRANT SELECT and GRANT EXECUTE is huge:
While first permission allows only select from table/view, GRANT EXECUTE allows exeuting procedure which can perform SELECT and INSERT/UPDATE/DELETE if required. Now, how many application for databases (except Reporting ones) require exclusively read access and never update?
I think we are at cross-purposes on this point (ie: agreeing with each other without realising it)
my point was that combining correct literal encoding with GRANT SELECT is impervious to "sql injection" attacks when reading data
in other words a SELECT can not become SELECT [field] FROM
;DELETE FROM
as those permissions do not exist
it could become SELECT [field] FROM
;exec spDeleteRow 'another id';
but that is even easier to achieve with editing URLs:
http://host/script?cmd=remove&recordid=<another record id>
which probably 90% of all webapps will succumb to
pro web apps digitally sign their URLs, form fields and cookies (and include the response handler's version no) to stop this
August 12, 2010 at 5:53 am
Actually, the data modifications is much rare form of SQL Injection used by hackers. Mostly they whould be interesting just in basic selects...
For example: calssical injecting of "1=1; select * from sys.objects;" would give them idea about database structure. Then they could query some tables eg. ones which may store classiffied data (eg. client/customer details or anything else).
Will you be surprised to know that most of the hacked databases will be Oracle - not SQLServer? It is due to the fact that dynamic SQL of this sort is much more used by Oracle devlopers then by SQLServer ones. Every now and then, best practice fot SQL developers was and is to use stored procedure for accessing data and, in case of dynamic SQL, use of SP_EXECUTESQL which allows passing parameters into dynamic SQL and defends against SQL Injection. Wide use of ORM tools is quite recent "moda" in SQLServer/.NET world, these tools are written with very good protection against SQL Injection and actually ORM design concept does prevent it itself.
OP has stated that their database contains only 50 tables but 1000+ procedures, 400+ views and etc. Yes, these numbers do look strange.
However, as was mentioned before, it does not mean that the move into dynamic in-line sql is a good and right choice. Replacing 1000+, could be badly designed and written, procedures by moving SQL into C# code would never be advised by any normal software architect or experienced developer (not even talking about MS ones).
August 12, 2010 at 7:53 am
my point was that combining correct literal encoding with GRANT SELECT is impervious to "sql injection" attacks when reading data
Yes, but sometimes users need the capability to UPDATE and, on occasion, even DELETE rows, as part of applications.
Scott Pletcher, SQL Server MVP 2008-2010
August 12, 2010 at 8:29 am
I'd like to know how the server handles large quantities of distinct SQL statements with SP_EXECUTESQL
seems to me that all those cached plans will end up pushing out plans that really will be used again
isn't caching the plan only good when:
a) the SQL statement has a high probability of high frequency re-use
b) the resultant plan is not parameter dependent
is there a way to use sp_executesql and never cache the resulting plan?
so you just get the benefit of parameters
August 12, 2010 at 9:09 am
DataDog (8/12/2010)
I'd like to know how the server handles large quantities of distinct SQL statements with SP_EXECUTESQLseems to me that all those cached plans will end up pushing out plans that really will be used again
isn't caching the plan only good when:
a) the SQL statement has a high probability of high frequency re-use
b) the resultant plan is not parameter dependent
is there a way to use sp_executesql and never cache the resulting plan?
so you just get the benefit of parameters
1. SQL Server is well designed to handle large quantities of cached plans.
2. SQL Server will cash plan for any query, no matter where it's coming from. For sp_executesql it will cach query plan as one even if sql to execute contains multiple queries. This plan will be reused if you run the same query with different parameters.
3. Queries from dynamic sql executed using EXEC() or in-line sql called from client app, also will be compiled and their plan will go into cach, however it will be less likely to be reused as any difference in query (any additonal space anywhere, not even talking change in WHERE conditions) will make SQLServer to avoid using cached plan and will cause query compilation (and new plan caching) again, so using such techincs IS a wasting of resources.
If you don't want SQLServer to cach query plan add "OPTION(RECOMPILE)" to the end of your query!
But I can't understand why you don't want to benefit from query plan caching which is one of the most important features of SQLServer.
August 12, 2010 at 9:25 am
SQL Server will cash plan for any query, no matter where it's coming from.
That's not true.
Scott Pletcher, SQL Server MVP 2008-2010
August 12, 2010 at 10:05 am
scott.pletcher (8/12/2010)
SQL Server will cash plan for any query, no matter where it's coming from.
That's not true.
Wrong wording used: MS will cash anything it possibly can.:-D
Ok, SQLServer will cache the query plan if it is possible regardless of where the query is coming from.
But, there are ways to tell to SQLServer not to cache the plan.
Here is a good fresh read about it fro SQL2008:
http://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx
August 12, 2010 at 10:09 am
Certain conditions make a plan uncacheable as well.
For example, a literal > 8K. That used to be just a weird quirk, not really applicable in real life ... but with XML, I could see someone coding up their own literal, to be used as XML, that exceeded 8K, preventing SQL from caching the plan.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply