October 12, 2018 at 4:46 pm
FIXED, man.... fixed....
I didn't put the DLL there and I didn't ran the query to enable it. :Whistling:
By the way the error
Incorrect syntax near the keyword 'with'.
is definitely misleading.
It should say something like "hey dude, no DLL found" or "no idea what you are talking about". Instead it looked more like a typo in the query.
Anyway, this was an interesting chat indeed. I think I will use your DLL for my private work at home and I will keep using "MSXML2.ServerXMLHTTP.6.0" on machines where I'm not allowed to install DLLs.
So we have 2 solutions for 2 different scenarios .
This is great .
Thank you
October 13, 2018 at 10:12 am
I was going to suggest you could wrap things up in a table-valued function so you could query something like this:
SELECT * FROM [dbo].[ufn_example_by_area_amenity]('Auckland', 'cinema')
Function would look something like this:
CREATE FUNCTION [dbo].[ufn_example_by_area_amenity]
(
@area NVARCHAR(MAX),
@amenity NVARCHAR(MAX)
)
RETURNS TABLE AS RETURN
(
SELECT
[type],
[id],
[lat],
[lon],
[amenity],
[name]
FROM OPENJSON
(
[dbo].[clr_http_request]
(
'GET',
'http://overpass-api.de/api/interpreter?',
CONCAT('data=[out:json];area[name=%22', @area, '%22]-%3E.a;(node(area.a)[amenity=', @amenity, '];way(area.a)[amenity=', @amenity, '];rel(area.a)[amenity=', @amenity, ']; );out;'),
NULL, 10000, 0, 0
).value('/Response[1]/Body[1]', 'NVARCHAR(MAX)'),
'$.elements'
)
WITH
(
[type] nvarchar(max) N'$.type' ,
[id] nvarchar(max) N'$.id',
[lat] nvarchar(max) N'$.lat',
[lon] nvarchar(max) N'$.lon',
[amenity] nvarchar(max) N'$.tags.amenity',
[name] nvarchar(max) N'$.tags.name'
)
);
October 15, 2018 at 12:18 pm
yolousa - Thursday, October 11, 2018 9:53 AM...but the overall idea of integrating Web API calls in database implementation may do more harm than good. There are levels of "safety" for CLR code and I am almost sure HTTP libraries will require CLR integration as unsafe. That in itself is not bad but has potential for harmful implementations. Again, interesting piece of code, nice writing, good job! Just understand the implications from an architectural standpoint, and be careful.
Hi yolousa. This warning is highly generic and not very helpful given that it could just as easily be said about: cursors, triggers, MERGE, LEFT JOINs, WITH (NOLOCK), index hints, etc, etc. If there is a specific danger you are aware of, then please provide that so this can be a meaningful warning. Otherwise it is unfair and misleading because the real issue comes down to appropriate usage. And the author was not suggesting anything that would be specifically concerning. Ever since SQLCLR was introduced in SQL Server 2005, many people have been unfairly negative about this feature due to either lack of information and/or misinformation. Just saying that it is "unsafe" doesn't really help. Your passive statement about HTTP libraries needing to be marked as UNSAFE is an example of this pervasive misinformation. All that is necessary is to mark the custom library as EXTERNAL_ACCESS, not UNSAFE. Yet posts such as this keep people from using this feature in situations where it would be appropriate. For example, francesco (in other posts in this thread) has stated that his company does not allow SQLCLR in Production, yet his current process -- still a web request -- is using OLE Automation stored procedures (sp_OA*) which are less stable, less secure, less efficient, and less functional (they don't work with any datatypes introduced in SQL Server 2005 or newer) than SQLCLR.
For more information on SQLCLR, including appropriate usage and security, please see the Stairway to SQLCLR series I am writing here on SQL Server Central: Stairway to SQLCLR.
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
October 15, 2018 at 12:41 pm
I was thinking similarly about the OLE Automation. That is also a configuration which is turned off by default just like CLR and once enabled allows you to do so many different things while CLR being enabled by itself doesn't do much. One still need to register assemblies and create objects to use them. Especially if the TRUSTWORTHY database configuration is avoided I'm having a hard time thinking of anything inherently insecure.
That said, I'm very interested to hear about security concerns related to this [clr_http_request] function, provided they're specific and not a blanket "CLR opens up security concerns"
Hopefully nobody uses this function to mount DDOS attacks :blink: (although, I imagine even that'd be rather ineffective)
October 15, 2018 at 1:22 pm
francesco.mantovani - Thursday, October 11, 2018 9:08 PMEilert Hjelmeseth - Thursday, October 11, 2018 8:50 PMIndeed, some policies (especially in larger corporate environments) may to some extent block this method. For those that allow it this can be a very convenient tool, though!This does fall into the "UNSAFE" category, but then all CLR in starting in SQL Server 2017 is considered UNSAFE. See CLR strict security: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security
Additionally, I was careful to do some homework on this one and find out how to "properly" sign the assembly so I wouldn't need to do things like set TRUSTWORTHY on the database.
CLR, just like other excellent features of SQL Server like dynamic sql, xp_cmdshell, OPENQUERY, etc. need to be handled with care!yeah, that's why my horrible script you can see I posted on this page doesn't use xp_cmdshell but sp_OACreate
and from another post of yours (francesco):
Anyway, this was an interesting chat indeed. I think I will use your DLL for my private work at home and I will keep using "MSXML2.ServerXMLHTTP.6.0" on machines where I'm not allowed to install DLLs.
So we have 2 solutions for 2 different scenarios .So we have 2 solutions for 2 different scenarios .
Hi Eilert. Nice article. Good explanation and integration with JSON and XML. Just to clear up two things:
Take care,
Solomon..
P.S. If Eilert's code works for you then great (and it is more thorough than any other example I have ever seen posted). But just to mention, while not taking anything away from the usefulness of what the author has provided, there is a SQLCLR TVF called INET_GetWebPages in the SQL# library (that I wrote) that does this and handles a few more edge cases, as well as taking into account performance when the code is expected to be executed for concurrent sessions and hitting the same URI (the same host, that is). INET_GetWebPages is only available in the Full (i.e. paid-for) version, but that might be worth it depending on the need / usage.
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
October 15, 2018 at 1:32 pm
Thanks for the additional clarity (and kind words), Solomon!
October 15, 2018 at 2:00 pm
Eilert Hjelmeseth - Monday, October 15, 2018 12:41 PMI was thinking similarly about the OLE Automation. That is also a configuration which is turned off by default just like CLR and once enabled allows you to do so many different things while CLR being enabled by itself doesn't do much. One still need to register assemblies and create objects to use them. Especially if the TRUSTWORTHY database configuration is avoided I'm having a hard time thinking of anything inherently insecure.That said, I'm very interested to hear about security concerns related to this [clr_http_request] function, provided they're specific and not a blanket "CLR opens up security concerns"
Hopefully nobody uses this function to mount DDOS attacks :blink: (although, I imagine even that'd be rather ineffective)
Correct, there is nothing inherently insecure about SQLCLR. But that doesn't stop many people from spreading the ideas that SQLCLR is slow and/or insecure, without any actual evidence to back it up.
If set to EXTERNAL_ACCESS, there shouldn't be any security concerns. And even if your assembly is set to UNSAFE, it isn't doing anything that could be harmful. It's not like someone can edit the registry via HttpWebRequest. And if someone did want to edit the registry, it would be easier to do via xp_cmdshell or sp_OA* procs. If the service account has rights to edit the registry, then someone could slip in an ALTER PROC to update code to do something. Although using Module Signing would disallow sneaky updates by automatically removing permissions if the proc is altered in any way :). Either way: yes, xp_cmdshell and sp_OA* procs can be secure enough if restricted to a few procs that cannot be easily altered. But, SQLCLR allows for both a) more granular control, and b) impersonation of the calling Windows Login.
Regarding DDOS attacks: .NET by default kinda restricts this. By default you are not allowed to have more than 2 concurrent requests to the same URI, at least not in SQL Server's CLR host, and I believe not in ASP.NET either. I think the default is 20 for Windows / console apps. And this is typically why web requests via SQLCLR are known for being "slow". But even if you change that setting, SQL Server only has so many threads (i.e. max-workers) so you would be much better off with a console app to stage a DDOS attack (maybe you can kick off a few via xp_cmdshell 😉 ).
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
October 15, 2018 at 2:09 pm
Eilert Hjelmeseth - Monday, October 15, 2018 1:32 PMThanks for the additional clarity (and kind words), Solomon!
Yer welcome. And no problem: you did a good job on it. You handle GET vs POST data, setting of custom headers that have to be set via the individual request properties, pass back the varying number of response headers, etc. Only one thing I would say should be changed (and it's rather minor): input parameters should be using SqlString instead of regular string. SqlString handles NULLs (like string? ) but also has properties containing Collation info (LCID, SqlCompareOptions), as well as methods like GetUnicodeBytes and GetNonUnicodeBytes, etc. To get the native .NET string out of SqlString, just use SqlStringParam.Value (all Sql* types have a Value property that returns the expected native .NET type).
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
October 15, 2018 at 2:54 pm
Hi Solomon, I tell you what is the scenario:
- you have a few thousands customers, each one with a different security policy
- in case of priority support you need to debug the problem in less than 1 hour without installing .EXE files or dropping .DLL
- you have no write to enable xp_cmdshell
- you basically need to do API calls between machines, within the customer network, to see what is going on and what the calls are returning.
Forget about the OpenStreetMap call I pasted as example, that is my private life and has nothing to do with my job.
Please let me know if you have a better way out for me, I will adopt it for sure.
(I also have a PowerShell script that does the same but the output in the terminal is horrible to debug and fry my eyes, SSMS is much better)
Thank you
October 15, 2018 at 3:14 pm
SqlCLR, Ole Automation, ssis usage in and of itself is not insecure in my opinion, even loading and using an http stack. These tools are very useful provided certain limits are met. Internet connection is insecure, a no internet policy for database is what should be religiously adhered to, but intranet only is just fine though. Secondly, I find that they are better suited for "staging" servers than busy production servers as they all share the same memory space as sqlserver.exe and can destabilize a busy production server. Yes, even SSIS shares memory space with sqlserver.exe; true even when ssis is run on a server remote to the sqlserver. Remains to be seen what the latest security scanning engines say about them though. So far none of my sqlservers being scanned by the security folks have any such tools so I'm not sure, all have app servers in front. But they give me very powerful tools for when I need fast iterative procedural processes which sqlserver is very poor at.
October 24, 2018 at 3:30 pm
Eilert, using CLR to call an HTTPWebRequest can be dangerous for your server. If your web call exceeds the thread quantum the thread manager will attempt to put it into quantum punishment. As it is CLR and not sql it will not be able to do so. Then what will happen is that the thread manager will get very backed up very fast.
https://www.mssqltips.com/sqlservertip/4403/understanding-sql-server-schedulers-workers-and-tasks/
This stack exchange article talks about the various ways to avoid quantum punishment from a CLR library.
https://dba.stackexchange.com/questions/164891/sql-server-clr-procedures-and-sqlclr-quantum-punishment
To break them down:
Thread.Sleep(0) , however for your webrequest would require you to call it with asynch and loop calling thread.sleep until the request returned.
and Thread.BeginAffinity (requires unsafe)
I know this personally as I have crashed a SQL Server with a CLR library that implemented regex and someone used a regex that had catastrophic backtracking in it. Good article otherwise. If you make those changes to your CLR you will also keep it from potentially dragging a server to its knees. 😀
October 24, 2018 at 4:29 pm
spcghst440, thanks for the info!
I'll check that out and will probably make some tweaks to avoid that.
October 25, 2018 at 11:22 pm
Eilert Hjelmeseth - Wednesday, October 24, 2018 4:29 PMspcghst440, thanks for the info!
I'll check that out and will probably make some tweaks to avoid that.
Eilert: I wouldn't be in such a hurry to make either of those changes. You might want to test first to verify the validity of the potential issue. I have seen countless posts warn against possible locking up of a system due to web service calls, yet I have seen none actually prove it. Sure, it might be possible, but it would require locking up all workers. How many concurrent web service calls will there realistically be in scenarios using them (and using them in such a way that they don't return very quickly due to calling a service sitting on the same internal network?). And, there is a CLR_Quantum_Punishment that occurs for CLR-threads (this was part of the discussion in that DBA.StackExchange Q&A that spcghst440 linked to. Also, doing any Async functionality will also require that the Assembly be marked as UNSAFE.
While I am not disagreeing that RegEx catastrophic backtracking is rather bad, there are wo things to consider:
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
October 31, 2018 at 4:03 pm
Has anyone been able to compile this for .NET 3.5? I get a slew of errors, and they mostly look like this is using functions that aren't supported in 3.5:
Severity Code Description Project File Line Suppression State
Error CS0000 'string' does not contain a definition for 'IsNullOrWhiteSpace' ClrHttpRequest clr_http_request.cs 14 Active
Error CS0000 'HttpWebRequest' does not contain a definition for 'Date' and no extension method 'Date' accepting a first argument of type 'HttpWebRequest' could be found (are you missing a using directive or an assembly reference?) ClrHttpRequest clr_http_request.cs 53 Active
Error CS0000 'HttpWebRequest' does not contain a definition for 'Host' and no extension method 'Host' accepting a first argument of type 'HttpWebRequest' could be found (are you missing a using directive or an assembly reference?) ClrHttpRequest clr_http_request.cs 59 Active
Error CS0000 'Stream' does not contain a definition for 'CopyTo' and no extension method 'CopyTo' accepting a first argument of type 'Stream' could be found (are you missing a using directive or an assembly reference?) ClrHttpRequest clr_http_request.cs 150 Active
Error CS0000 'HttpWebResponse' does not contain a definition for 'SupportsHeaders' and no extension method 'SupportsHeaders' accepting a first argument of type 'HttpWebResponse' could be found (are you missing a using directive or an assembly reference?) ClrHttpRequest clr_http_request.cs 189 Active
I don't need OpenJSON to handle what I'm working on, and would rather not upgrade from 2008 just to get this working.
November 1, 2018 at 1:26 pm
jim-1056675 - Wednesday, October 31, 2018 4:03 PMHas anyone been able to compile this for .NET 3.5? I get a slew of errors, and they mostly look like this is using functions that aren't supported in 3.5:Severity Code Description Project File Line Suppression State
Error CS0000 'string' does not contain a definition for 'IsNullOrWhiteSpace' ClrHttpRequest clr_http_request.cs 14 ActiveI don't need OpenJSON to handle what I'm working on, and would rather not upgrade from 2008 just to get this working.
No, you don't need to upgrade in order to get a web request to work. This type of thing can work just fine even on SQL Server 2005.
Correct, those errors are due to the code using methods and properties that were not available in .NET Framework version 3.5. For example, the documentation for HttpWebRequest.Date indicates that it was added in Framework version 4.0. So, just remove those pieces.
As for string.IsNullOrWhiteSpace, you can simply replace that with string.IsNullOrEmpty(parameter.Trim()) and it will effectively be the same thing.
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
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply