The PythonService is a simple RESTful (well, as much as a 1-page service can be REST) web service that will run whatever python you give it. This is generally a bad idea. But it allows a user in a system with a weak scripting language, such as T-SQL, to easily solve problems that are cleaner in Python. As you can see in this stupid SQL trick.
I got this idea from the IPython NoteBook, which struck me as a cool, if potentially extremely dangerous, idea. However, as much as I like the way it is built, and the ability to run multiple threads, there's no clean way to interact with the IPython kernel. I want something that is extremely simple and easy to use, and so I wrote it just to see if I could.
To use the PythonService, make a GET request (or a POST) to / on the server in question, port 8080 (currently this is bound to http://localhost:8080/) with the body containing the JSON of the python request. You are not supposed to tunnel GET through POST, but the .Net HTTPWebRequest doesn't let you send a body, so we have to use this antipattern; even Roy Fielding says this is what you have to do, so it's all right.
The request should set the content-type and accept headers to application/json. Accepted methods are GET and POST. The body should be a JSON object containing these name/value pairs:
Command = The python code to execute.
Arguments = An optional object containing any variables that should be created before the command in Command has been executed. When the namespace in which the python code is to be executed is created, it is populated with these arguments.
ReturnArguments = An optional array containing any variables whose values after execution should be returned. This service is intended to do calculations and return data to the caller, so there should typically be at least one variable returned, but Python can be used to do other things than solve math problems, so this is not technically required.
PythonService returns a JSON object in the body of the HTTP response, containing these name/value pairs:
Status = The HTTP status (hopefully 200 OK).
Error = If there is an exception, this value will optionally hold the text of the exception.
ReturnValues = An object containing the variables and values requested by the caller. If the caller requests a value that is not found in the execution namespace (perhaps it there was a typo in the ReturnArguments array), then it is not returned at all. This is to distinguish it from a variable whose value is set to None during execution.
Outside of the standard library, PythonService depends on the bottle Python web framework and mimerender. Bottle is just very nice to use, and even though in this service I'm not making use of the ability to automatically render output for different callers (the main purpose of mimerender), I am still using mimerender so I can render exceptions as JSON rather than as HTML, which is what bottle uses.
PythonService takes very little setup to work. In fact, you can simply execute PythonService.py and it will be good to go. However, the script is currently set up to listen strictly on localhost:8080. If you want to change how the server is run in the last line of the script (bottle.run(app, host='localhost', port=8080, debug=True)), see the easy to understand documentation on Bottle's web site.
Download PythonService.py at the bottom of this page (Google is requiring a conversion to their new Sites format, which is probably going to eliminate that py file).
I mentioned that this was not the best of ideas. Imagine what can be done with a server that will willing to execute whatever you ask it to. Send all your company's data to Russia. Delete your server. Control a botnet. Host a porn site. We do not live in a nice world where you can trust such a service to the Internet.
If you do actually want to use such a dangerous service in production, you are going to need to lock it down as much as you can. I would recommend installing it on a Linux VM, as a regular user. Firewall off the VM so it can only be accessed by the IP addresses of valid users. A very nice method, which does use more resources on your SQL Server than you may want to, is to set up SQL Server as a VM host, the Linux server as a VM guest, with an internal network so that no other servers can contact the guest and the guest can not contact any other servers. But in whatever case, make sure that the PythonService runs as an unprivileged user on a machine that cannot contact the Internet (to transmit information or download and install new software) or be contacted by the Internet. Do not give it any special access to the database server. Treat it as an untrusted user on the network. And, just to be safe, once a week or once a day even, restore a VM snapshot, bringing it back to a virgin install. If you need to do OS updates, restore the snapshot, shut down the service, connect to the Internet, install updates, disconnect Internet, take a snapshot, and restart the service.
Does this sound like overkill? It's not. It's the best I can think of but I'm sure that real security experts, which I am not, can probably already poke holes in my little plan. It's very hard to lock down a service that is designed to execute untrusted code.
PythonService is Copyright 2014 Jeremy Lakatos, released under the GNU Public License version 3. It contains modules released under the MIT License (Bottle, Mimerender).