SQL queries through CGI for security : why ?

[bangkok]

Several times, members wrote that it was a good idea to send SQL query to a server through CGI, in order to achieve a good level of security (rather than having an application that connects directly to a MySQL server for instance, with login + password).

I fail to understand how.

Could someone elaborate ?

If someone finds or knows the URL of the CGI, than he could send SQL queries, right ?

So how it could be a good technical solution as far as security is concerned ?

[Janschenkel]

The primary problem with direct database connections, is older versions of the application potentially wreaking havoc in the database as they may be executing UPDATE queries that have changed in newer versions.

Secondly, most database servers prefer not to offer outside access (PostgreSQL is intially configured to only allow connections for the same host).

Thirdly, the cgi script shouldn't just accept any old query; you should have a separate 'facade' cgi for each query, and simply pass in the query parameters; then your cgi merges them with its template query definition and returns the result in the appropriate format (plain text, xml, ... - depending on an incoming param, for instance).

Finally, as the iOS verqsion does not (yet) have database driver support, your only option is top go through such a cgi to load and store data on your remote database.

HTH,

Jan Schenkel.

[FourthWorld]

Jan said it well. SQL is a well-known language, and MySQL is a well-known implementation. Providing open-ended access to it makes your app fully dependent on MySQL's security features, and exposes your app to any weaknesses as they are discovered. What can I say? All software always has bugs, and anything as complex as MySQL will have exposures.

Consider:
http://www.google.com/search?q=mysql+zero-day+exploit

With a CGI between the client and your database you get an extra layer of protection. You can limit the specific types of commands being executed, perform your own parameter validation, even provide your own user access control.

This is not just a LiveCode thing. Most experienced PHP developers will make the same recommendation. There's always someone out there who knows more about MySQL than you do, and they can use that knowledge against your server. But no one knows your custom CGI as well as you.

Extra bonus points: One of the most common weakness is complex systems is buffer overruns, but historically the LiveCode engine has been designed in a way that obviates such issues - here's a quote from the original inventor of the LiveCode engine on this:
http://article.gmane.org/gmane.comp.ide ... r+overruns

[bangkok]

[sorry for my late answer]

Thank you for your comments and the idea of "extra layer", in order to keep control.

But my question was related to sensitive data. for instance, i've got one CGI that sends a SQL query that returns let say the turn over of my company. I don't want anybody to be able to call that CGI and to get the data, right ?

On the other hand, I don't want to put a SQL login+password embeded into my application.

So what are my options ?

I was thinking about a scheme :

-in the application, the user types his login+password

-the application calls a CGI, a SQL query checks the login + pwd, and if there is a match, returns a special key (created on the fly and pasted into the MySQL db)

-and after that for each other CGI, I pass this key at the end of the URL.

-and the CGI makes a check on the key

-and then when the user would quit the application, another CGI would delete the key on the distant DB.

Something like that ?

What do you think ?

[Janschenkel]

The web server with the CGI should always be called using the https protocol if the data is sensitive.
Using a unique session identifier token will definitely help security, reducing both the number of times the user id and password are sent over the wire, and the risk of spoofed calls to your CGI.

Jan Schenkel.