August 16, 2018 at 6:33 am
This also applies to Jeff's comments. The original comment said a simple check of permissions. How is this "simple check" done.
These are all interesting comments, but they don't address how this "simple check" is done. What is this check? Are you just checking the code before it goes into production? I had the impression it is simple to make some check before executing the statement. If this is correct, what is that simple check?
August 16, 2018 at 7:06 am
RonKyle - Thursday, August 16, 2018 6:33 AMThis also applies to Jeff's comments. The original comment said a simple check of permissions. How is this "simple check" done.
These are all interesting comments, but they don't address how this "simple check" is done. What is this check? Are you just checking the code before it goes into production? I had the impression it is simple to make some check before executing the statement. If this is correct, what is that simple check?
Ah. True enough.
I believe that what Perry was originally talking about is a manual check of what permissions the login for things like the Application have. The specific example was "Why give an account create table in the first place?" and that's absolutely spot on. The inference here is the idea of minimizing the attack surface by granting only those privs that are absolutely required to use the Application. Unfortunately, may Applications are design without such regard because (especially people with a poor SQL Server background) are especially loath to do things like write stored procedures when extraordinary actions are required. They take the attitude that if it can't be done thru the ORM (pick a flavor), they want nothing to do with it.
IMHO, a truly secure application only needs privs to connect and then have privs to execute only those stored procedures necessary for the app to do the job. Even db_datareader and db_datawriter privs are totally unnecessary except to satiate people that have an ORM fixation. And, no... it is patently NOT necessary to write C.R.U.D. for every bloody table. Done correctly, there should be no C.R.U.D.-only procs.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2018 at 7:33 am
Jeff Moden - Thursday, August 16, 2018 7:06 AMRonKyle - Thursday, August 16, 2018 6:33 AMThis also applies to Jeff's comments. The original comment said a simple check of permissions. How is this "simple check" done.
These are all interesting comments, but they don't address how this "simple check" is done. What is this check? Are you just checking the code before it goes into production? I had the impression it is simple to make some check before executing the statement. If this is correct, what is that simple check?
Ah. True enough.
I believe that what Perry was originally talking about is a manual check of what permissions the login for things like the Application have. The specific example was "Why give an account create table in the first place?" and that's absolutely spot on. The inference here is the idea of minimizing the attack surface by granting only those privs that are absolutely required to use the Application. Unfortunately, may Applications are design without such regard because (especially people with a poor SQL Server background) are especially loath to do things like write stored procedures when extraordinary actions are required. They take the attitude that if it can't be done thru the ORM (pick a flavor), they want nothing to do with it.
IMHO, a truly secure application only needs privs to connect and then have privs to execute only those stored procedures necessary for the app to do the job. Even db_datareader and db_datawriter privs are totally unnecessary except to satiate people that have an ORM fixation. And, no... it is patently NOT necessary to write C.R.U.D. for every bloody table. Done correctly, there should be no C.R.U.D.-only procs.
OK, thanks. So there is no "simple check." You have to look at the bigger picture.
August 16, 2018 at 8:21 am
RonKyle - Thursday, August 16, 2018 7:33 AMJeff Moden - Thursday, August 16, 2018 7:06 AMRonKyle - Thursday, August 16, 2018 6:33 AMThis also applies to Jeff's comments. The original comment said a simple check of permissions. How is this "simple check" done.
These are all interesting comments, but they don't address how this "simple check" is done. What is this check? Are you just checking the code before it goes into production? I had the impression it is simple to make some check before executing the statement. If this is correct, what is that simple check?
Ah. True enough.
I believe that what Perry was originally talking about is a manual check of what permissions the login for things like the Application have. The specific example was "Why give an account create table in the first place?" and that's absolutely spot on. The inference here is the idea of minimizing the attack surface by granting only those privs that are absolutely required to use the Application. Unfortunately, may Applications are design without such regard because (especially people with a poor SQL Server background) are especially loath to do things like write stored procedures when extraordinary actions are required. They take the attitude that if it can't be done thru the ORM (pick a flavor), they want nothing to do with it.
IMHO, a truly secure application only needs privs to connect and then have privs to execute only those stored procedures necessary for the app to do the job. Even db_datareader and db_datawriter privs are totally unnecessary except to satiate people that have an ORM fixation. And, no... it is patently NOT necessary to write C.R.U.D. for every bloody table. Done correctly, there should be no C.R.U.D.-only procs.
OK, thanks. So there is no "simple check." You have to look at the bigger picture.
IMHO, that's mostly correct. The word "simple", I believe, comes from the fact that all you need to start with is to simply manually check the privs of the login/user for the Application. That will let you know what the condition of the bigger picture actually is and, possibly, who can get the DBA fired. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2018 at 8:41 am
In my view, that's not a "simple check." I might suggest the poster have said "a check of this, that, and this other thing" will prevent this. I don't know that what you describe is "simple". Sometimes posters can state something that however obvious it may seem to them, it may not be similarly so obvious to others. I don't recall that the person who posted that clarified the statement, though I'll go back later and re-read the comments to be sure.
August 16, 2018 at 8:59 am
Jeff Moden - Thursday, August 16, 2018 6:07 AMThom, thanks again for taking the time to write this article. It's not only a great "Back to Basics" article but it turned out to be an "I Learned Something New" article for a lot of good folks. It's also made for quite the interesting discussion that I think a whole lot of people appreciate because it has its roots in one of the most important aspects of security there is, SQL Injection. My hat is off to you and everyone, regardless of the stance they've taken, because the discussion poses questions that a whole lot of people simply could never anticipate when writing an article. This is why these types of articles can be a thousand times more helpful than Books Online and one of the reasons why I love the SQLServerCentral community and the SQL Server community in general.
Thanks Jeff, I really appreciate the feedback. You're right that the discussions that come out from some of the articles is really important; sometimes more so than the article itself. your delimtiedSplit8k function was a great example of the community working together to produce a really great function, that (in my opinion) is still better that Microsoft's own (most especially because STRING_SPLIT doesn't return the ordinal position).
I've certainly learned a couple of bits in the discussion as well, but it's also reminded me of things I should have already known, like that QUOTENAME returns a nvarchar(256), not (MAX() or (4000). BOL is great for a reference tool, but (unfortunately) there are times when it's incomplete, or even wrong, but combined with the right community, and complimentary articles, you can get some really comprehensive learning.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2018 at 9:41 am
Thom A - Thursday, August 16, 2018 8:59 AMJeff Moden - Thursday, August 16, 2018 6:07 AMThom, thanks again for taking the time to write this article. It's not only a great "Back to Basics" article but it turned out to be an "I Learned Something New" article for a lot of good folks. It's also made for quite the interesting discussion that I think a whole lot of people appreciate because it has its roots in one of the most important aspects of security there is, SQL Injection. My hat is off to you and everyone, regardless of the stance they've taken, because the discussion poses questions that a whole lot of people simply could never anticipate when writing an article. This is why these types of articles can be a thousand times more helpful than Books Online and one of the reasons why I love the SQLServerCentral community and the SQL Server community in general.Thanks Jeff, I really appreciate the feedback. You're right that the discussions that come out from some of the articles is really important; sometimes more so than the article itself. your delimtiedSplit8k function was a great example of the community working together to produce a really great function, that (in my opinion) is still better that Microsoft's own (most especially because STRING_SPLIT doesn't return the ordinal position).
I've certainly learned a couple of bits in the discussion as well, but it's also reminded me of things I should have already known, like that QUOTENAME returns a nvarchar(256), not (MAX() or (4000). BOL is great for a reference tool, but (unfortunately) there are times when it's incomplete, or even wrong, but combined with the right community, and complimentary articles, you can get some really comprehensive learning.
Learned things as well, even before your article. I used to just build the '[' and ']' within the strings when creating dynamic SQL from the system tables. Then I ran into an index name that contained a '[' in the name (stupid people doing stupid things in production and not telling anyone) that broke code that had been working for several years. QUOTENAME is now a staple in all of my code that builds dynamic SQL even if there is NO user input. Sometimes you learn things the hard way.
Great article.
August 16, 2018 at 3:29 pm
Thom A - Wednesday, August 8, 2018 7:12 AMThom A - Monday, August 6, 2018 2:22 AMJeff Moden - Sunday, August 5, 2018 8:53 AMThe unfortunate part of that is that, once again, MS failed to provide complete documentation for something that could be incredibly useful just based on what their perception of what is "useful" might actually be. :sick: Either that or the people writing the documentation just don't know what the capabilities actually are.
There's quite a few points in the documentation like that now, where it's not quite "accurate", or actually just completely misses some functionality (intended or not). I do wonder if Microsoft are almost wanting the community to do the updating, as you can submit an edit for an article via their Github (For example sql-docs/quotename).
Seems that that is Microsoft's attitude. Submitted a Pull Request on Monday, and they've already updated the documentation with the new wording I submitted:
Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), a double quotation mark ( " ), a left or right parenthesis ( () ), a greater than or less than sign ( >< ), a left or right brace ( {} ) or a backtick ( ` ). NULL returns if an unacceptable character is supplied. If quote_character is not specified, brackets are used.
I'm actually pleasantly surprised at how quick the turnaround time on that was. I was expecting it to take at least a month or 2; or at worst after the image, text and ntext datatypes were finally removed in SQL Server 2052. 🤣
Thanks for updating that documentation, Thom. I wouldn't think of it in terms of MS being lazy and pushing off the work to suckers, er, I mean, "volunteers" 😉 , though. Developers don't usually write the documentation, at least not at most companies (that I am aware of). And the tech writers that do write the documentation are, I would expect, not programmers, though hopefully technically competent. But they only know what they are told, and it is not surprising that there are large gaps in the documentation, and mistakes, and stuff that is out of date.
For example, the Collation team came out with a bunch of new Japanese Collations in SQL Server 2017. They also did something awesome (mainly that it just made so much sense) and included support for Supplementary Characters in all of the new Collations (anything at version 140): there is no separate variation of the new Collations that end in "_SC". Those variations that came out with SQL Server 2012 were necessary due to the Collations already existing; they used the version 100 Collations that came out with SQL Server 2008 but added the "_SC" option such that the non-"_SC" variations continued to function exactly as they had been doing. But there is never any reason to not want Supplementary Character support, so it is just magically there (as it should have been a long time ago). BUT, they only told the team that handles the installer / setup. The setup program knows that the new Collation have Supplementary Character support and so that option is disabled when choosing the Instance-level Collation only if you select one of the new version 140 Collations. They did not tell the documentation team as there was absolutely no mention of this anywhere. So, I did the updates (documented here: All New Collations in SQL Server 2017 Implicitly Support Supplementary Characters). I even updated the COLLATIONPROPERTY documentation to include the fact that there was indeed another version (and while I was at it, I included links so that people might finally have an easy way to get LCID and Code Page info, and listed the items that make up the ComparisonStyle bitmask). However, I ran out of time and energy and could not bring myself to update at least one of the examples, which are still identical ;).
Like you, I have had a positive experience updating the documentation as the editors are helpful and understanding. And I would encourage others to contribute as well, since we often see things in practice that testers and the documentation team don't.
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
August 17, 2018 at 12:51 am
peter.row - Tuesday, August 14, 2018 7:18 AMJeff Moden - Tuesday, August 14, 2018 6:28 AMpeter.row - Tuesday, August 14, 2018 4:06 AMWhat I'd really like is a QUOTENAME function that only quoted if the name required it because the name being used had spaces, represents a keyword etc...
Why? Because I have some scaffolding scripts that generate SQL scripts based on a table schema etc... The resulting script is then saved and used and is under version control.I really hate reading through SQL full of [tablename].[column] formatting it is totally distracting to me. I only quote stuff when needed for SQL to understand it or for SSMS/SOS not to highlight it as a keyword.
I use sp_executesql @theSQLCommand, @ParamsAsString, @Param1Value, @Parm2Value... ParmXValue to avoid SQL injection when using dynamic SQL.
I'd love that option but it would have to be an option so that I can support shops that require the damned brackets, which I also hate. 😀
Well they can use QUOTENAME and we can use QUOTENAMEIF, 🙂 - we can dream, :Wow:
Why dream about it when you can have it? 😉 The hardest part is making sure that the characters in the name conform to the actual rules of T-SQL identifiers. I emphasized "actual" since the current documentation is a bit inaccurate regarding which characters are valid or invalid. Fortunately, however, I did a bit of research on this topic and published the results here:
Completely Complete List of Rules for T-SQL Identifiers
The list of valid characters is quite large and is not conducive to being handled with a LIKE function (though I suppose not impossible). However, Regular Expressions (via SQLCLR) make this quite easy. You can download a free library of SQLCLR functions, SQL# (which I created), which contains several RegEx functions. For this we can use the RegEx_ReplaceIfMatched4k function, which only differs from the standard RegEx Replace in that if a match is not found, it will return a user-provided substitution value. This will allow us to match on the pattern of a valid regular identifier, and if found, return that match untouched (which is what the $_ does -- see examples below), else return the value being tested wrapped in QUOTENAME to surround it with square brackets and escape a single "]" into a double "]]".
The pattern is derived from one of my posts detailing the research that went into coming up with that complete list of rules for identifiers (towards the end):
The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2
And that pattern is:\A[\p{L}\p{Nl}\u0023\u005F\uFF3F\u0040][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*\Z
With that we can do the following:
DECLARE @test-2 sysname = N'Fg] g',
@Pattern NVARCHAR(500) =
N'\A[\p{L}\p{Nl}\u0023\u005F\uFF3F\u0040][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*\Z';
SELECT SQL#.RegEx_ReplaceIfMatched4k(@Test, @Pattern, N'$_', QUOTENAME(@Test), -1, 1, NULL), QUOTENAME(@Test);
-- [Fg]] g] [Fg]] g]
SET @test-2 = N'SelecT';
SELECT SQL#.RegEx_ReplaceIfMatched4k(@Test, @Pattern, N'$_', QUOTENAME(@Test), -1, 1, NULL), QUOTENAME(@Test);
-- SelecT [SelecT]
SET @test-2 = N'1SelecT';
SELECT SQL#.RegEx_ReplaceIfMatched4k(@Test, @Pattern, N'$_', QUOTENAME(@Test), -1, 1, NULL), QUOTENAME(@Test);
-- [1SelecT] [1SelecT]
Please note that we are using the "4k" version of the function, which indicates that any NVARCHAR input parameter or return value is NVARCHAR(4000) and not NVARCHAR(MAX). Using the "MAX" datatype is a definite performance hit that is unnecessary when input values will nearly always be a max of 128 characters.
All you need to do is handle the reserved words :).
I will see if I can add a function for this (including the handling of reserved words) in the next version of SQL#.
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
April 14, 2020 at 11:15 am
peter.row - Tuesday, August 14, 2018 7:18 AMJeff Moden - Tuesday, August 14, 2018 6:28 AMpeter.row - Tuesday, August 14, 2018 4:06 AMWhat I'd really like is a QUOTENAME function that only quoted if the name required it because the name being used had spaces, represents a keyword etc...Why? Because I have some scaffolding scripts that generate SQL scripts based on a table schema etc... The resulting script is then saved and used and is under version control.
I really hate reading through SQL full of [tablename].[column] formatting it is totally distracting to me. I only quote stuff when needed for SQL to understand it or for SSMS/SOS not to highlight it as a keyword.
I use sp_executesql @theSQLCommand, @ParamsAsString, @Param1Value, @Parm2Value... ParmXValue to avoid SQL injection when using dynamic SQL.
I'd love that option but it would have to be an option so that I can support shops that require the damned brackets, which I also hate. 😀
Well they can use QUOTENAME and we can use QUOTENAMEIF, 🙂 - we can dream, :Wow:
Why dream about it when you can have it? 😉 The hardest part is making sure that the characters in the name conform to the <i>actual</i> rules of T-SQL identifiers. I emphasized "actual" since the current documentation is a bit inaccurate regarding which characters are valid or invalid. Fortunately, however, I did a bit of research on this topic and published the results here:
Completely Complete List of Rules for T-SQL Identifiers
The list of valid characters is quite large and is not conducive to being handled with a LIKE function (though I suppose not impossible). However, Regular Expressions (via SQLCLR) make this quite easy. You can download a free library of SQLCLR functions, SQL# (which I created), which contains several RegEx functions. For this we can use the RegEx_ReplaceIfMatched4k function, which only differs from the standard RegEx Replace in that if a match is not found, it will return a user-provided substitution value. This will allow us to match on the pattern of a valid regular identifier, and if found, return that match untouched (which is what the $_ does -- see examples below), else return the value being tested wrapped in QUOTENAME to surround it with square brackets and escape a single "]" into a double "]]".
The pattern is derived from one of my posts detailing the research that went into coming up with that complete list of rules for identifiers (towards the end):
The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2
And that pattern is:
\A[\p{L}\p{Nl}\u0023\u005F\uFF3F\u0040][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*\ZWith that we can do the following:
DECLARE @test-2 sysname = N'Fg] g',@Pattern NVARCHAR(500) =
N'\A[\p{L}\p{Nl}\u0023\u005F\uFF3F\u0040][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*\Z';
SELECT SQL#.RegEx_ReplaceIfMatched4k(@Test, @Pattern, N'$_', QUOTENAME(@Test), -1, 1, NULL), QUOTENAME(@Test);
-- [Fg]] g] [Fg]] g]
SET @test-2 = N'SelecT';
SELECT SQL#.RegEx_ReplaceIfMatched4k(@Test, @Pattern, N'$_', QUOTENAME(@Test), -1, 1, NULL), QUOTENAME(@Test);
-- SelecT [SelecT]
SET @test-2 = N'1SelecT';
SELECT SQL#.RegEx_ReplaceIfMatched4k(@Test, @Pattern, N'$_', QUOTENAME(@Test), -1, 1, NULL), QUOTENAME(@Test);
-- [1SelecT] [1SelecT]
Please note that we are using the "4k" version of the function, which indicates that any NVARCHAR input parameter or return value is NVARCHAR(4000) and not NVARCHAR(MAX). Using the "MAX" datatype is a definite performance hit that is unnecessary when input values will nearly always be a max of 128 characters.
All you need to do is handle the reserved words :).
I will see if I can add a function for this (including the handling of reserved words) in the next version of SQL#.
Take care, Solomon..
Try getting SQLCLR function installed on a server that you don't manage, e.g. a customers SQL Server - good luck with that.
April 14, 2020 at 12:42 pm
Thom, I really appreciate your sharing this. I've never heard of QUOTENAME until now! Very handy to add to my toolbox.
Rod
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply