April 14, 2021 at 4:42 am
Hi folks,
I have a peculiar problem I am trying to find a solution to. We have developed a process of importing data which requires validating against a web server URL, which returns either a 0 or a specific string on a page. There is nothing else on the page, no graphics, HTML, or CSS, just the value returned as shown on the screen shot.
Since we have written all the validation procedures in a stored procedure, it would be easer to incorporate it rather than writing a whole process on the front end as this is a one off for our client.
Sorry I cannot provide URL as this is hosted on an intranet.
Is there a way to read values from this page by just using SQL Server?
Any suggestions or help is greatly appreciated.
Thanks!
April 14, 2021 at 8:32 am
I don't think SQL should be allowed access to the web for security reasons.
I know you used to be able to, for some sort of internal validation process for certificates but if the server had no way to access the web it would just hang and we usually turned it off as the SQL Server would not be on a box that could easily be hacked into from the outside world.
You could create a Data Transformation Process using VBA code OR write a CLR stored proc in C# that does this but you would need to grant permission for your SQL Server to access the web, even if on an Intranet, and grant permissions through the firewall plus installing a browser onto your SQL Server which isn't really a good design.
If you really really wanted to, and had no other better way, you could install Internet Explorer on the DB server and then grant permission to an xp_cmdshell system proc - again something usually disabled for security purposes.
Then you could run a command to access your Internal URL by it's IP address passing the parameters to it with xp_cmdshell although it will make everything hang until the IE process is killed e.g
exec xp_cmdshell
'"C:\Program Files\Internet Explorer\IEXPLORE.EXE" http://198.168.2.3/validation.php?param=name&value=Joe%20Blogs'
I am sure you would be able to find a better way though.
Wouldn't it just be simpler to have a page in whatever language, I don't know say PHP, and have all the values you need to validate come out of a recordset from your DB that you can loop through, then call this remote validation page with a simple file_get_contents($url) call if there is nothing else returned on this Intranet validation page.
/* within the loop of values you have got to validate from the DB */
$validation_url = "http://localhost/validationpage.php?param=name&value=Joe%20Bloggs";
$result = file_get_contents($validation_url);
if($result==0){
// handle a failure response
}else{
//handle a success response
//maybe call your stored proc that saves the response back into your DB
}
April 15, 2021 at 4:38 am
Thanks for the response Rob.
I agree, SQL should not be able to access the web, but in this scenario the web service and the SQL server are contained within the local network. They will never be exposed to the internet. This is a staging SQL server and it does have xp_cmdshell enabled for accessing files in a temporary folder. If it ever got hacked, the data it contains would be of no value to the attacker as well as this data is already in the public domain. It would just be a matter of restoring the server image and carry on.
Since I work for a vendor, they have strict policy to use only the front end application, SQL, and command line. Otherwise it creates a support problem for future maintenance.
Personally, I would have wanted to hookup to the webservers DB and interrogate it that way, but then inner office politics come in.
I will try the approach you have suggested by using a CLR stored proc and document it as it still qualifies as being part of SQL. I completly forgot that this was an option in SQL server.
Cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply