How dangerous is it to provide a means for the public to run SELECT queries on a database? [closed]

Closed. This question is opinion-based. It is 开发者_StackOverflow社区not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 9 years ago.

Improve this question

Suppose I do the following:

• I create a MySQL database, and populate it with some data.
• I create a MySQL user who has access only to that database, and who only has SELECT privileges.
• I create a web page through which a user (any user, no password required) can enter arbitrary SQL, and on submitting the form, a script attempts to run the SQL as the MySQL user I created; any result set generated is displayed to the user; any error message generated is displayed to the user.
• Assume that the database contains no stored procedures etc, just tables and views, and that I am happy for anybody to see any of the contents of that specific database.

We assume that the setup will be probed by a malicious user. What is the worst that could happen?

Some thoughts:

• MySQL provides various statements like SHOW etc. that a user even having only SELECT privileges could use to gather information about the database server or about my databases. Other information could be obtained from error messages. While probably not sufficient to gain improper access, this information could surely help in doing so.
• There might be flaws in the database software, or in my scripts, or in the scripting language itself, that could allow a visitor to do things they are not supposed to be able to do through this interface.
• Doing this might violate a terms of service agreement, particularly if I am using shared hosting.

---

Hmmm. Clever users may attack via syntax like:

select some_function_that_updates() from some_table;

And there's a denial of service attack that could blow memory, like:

select * from some_massive_table cross join some_other_massive_table;

And frankly, it's hard enough for experienced programmers to write queries that behave well... what chance does a poor user have even if they try to write a good query

---

For this to work, you must write a "shell" application that actually does the queries. You're not going to turn people loose on SQL server directly. It's rude.

MySQL provides various statements like SHOW etc. that a user even having only SELECT privileges could use to gather information about the database server or about my databases.

Don't execute them in your shell.

Other information could be obtained from error messages. While probably not sufficient to gain improper access, this information could surely help in doing so.

Don't display them from your shell.

There might be flaws in the database software, or in my scripts, or in the scripting language itself, that could allow a visitor to do things they are not supposed to be able to do through this interface.

Don't do anything with "elevated" privileges. Don't execute anything other than SELECT in your shell.

Doing this might violate a terms of service agreement, particularly if I am using shared hosting.

Really unlikely. And. Don't ask us this kind of question. We don't know. Read your contract.

---

The thing you forgot.

A badly designed SELECT * FROM table, table, table, table can do an outer join of billions of rows, effectively becoming a denial of service attack.

Therefore, you must enable all of the "resource quota" features in the OS, and in SQL Server. Every quota must be set as small as possible and any resource quota problem leads to an immediate "too much data" kind of error page. No exceptions. No workarounds.

---

What is the worst that could happen?

Noone knowns. Not even the people who built the database engine could with absolute certainty say what would be possible.

What is certain however, is that you are removing a layer of protection. You no longer have a layer outside the database that can filter the input. You are relying entirely on the database's ability to protect itself, and it's not designed to be a public user interface.