54

I have a web api that connects to my SQL Server using a read-only connection and want to allow tech savvy users of my api to enter an SQL where clause on the querystring. I basically just want to tack what they enter onto the select statement.

Does a least-privilege (select ability on one table only), read-only connection to the database prevent all injection attacks?

Jens Erat
  • 23,446
  • 12
  • 72
  • 96
Aaron
  • 643
  • 1
  • 5
  • 5

4 Answers4

114

No. You might be confusing SQL injection with data injection; read-only tables do not prevent SQL injection and at best do only a little to limit its impact.

SQL injection simply means the ability to inject SQL code. While read-only tables may limit the ability to inject data into the table, they don't impact the ability to:

  • Read from other databases or tables if not disallowed
  • Read from system tables or run other system queries which are hard to disallow
  • Write excessively complex queries that will perform a DoS
  • Exfiltrate data using DNS
  • Access local files (e.g., utl_file in Oracle)
  • Access the DB server's network (e.g., utl_http in Oracle)
  • Execute arbitrary code on the server via DB function buffer overflows
  • See Advanced SQL Injection in Oracle databases for a good walk through all the sorts of things you need to worry about (and realize other databases have their equivalents)

If you

basically just want to tack what they enter onto the select statement.

then you're expressly permitting the attacker to try any of these.

Now, you can certainly do things to limit this. You can disallow quotes and SQL statement separator characters. You can disallow any input that's not [A-Za-z0-9"=] (or effectively equivalent for your database). But if you start going down this path, you're better off writing your application correctly: Expose a richer query interface where you offer the keys to be checked and then you perform proper quoting on whatever values the user enters.

T.J. Crowder
  • 975
  • 1
  • 5
  • 7
gowenfawr
  • 71,975
  • 17
  • 161
  • 198
  • Happy to be connected here, but couldn't all of these be mitigated by limiting the login to the DB to only be granted SELECT permission to the table in question? Then any other statement would fail? – RemarkLima Apr 27 '15 at 18:30
  • 8
    @RemarkLima, not necessarily - that's just the first bullet above. Depending upon the database variant, a user with `SELECT` access to one table may still be able to access system tables, system variables, functions, stored procedures... consider something like `' UNION ALL SELECT LOAD_FILE('/etc/passwd') --` (from [SQL Injection Wiki](http://www.sqlinjectionwiki.com/Categories/2/mysql-sql-injection-cheat-sheet/)) – gowenfawr Apr 27 '15 at 18:46
14

Does a least-privilege (select ability on one table only), read-only connection to the database prevent all injection attacks?

SQL injection is a way for an attacker to modify an existing statement so that it causes unintended actions. These actions might be changes on the database or code execution on the system but also simply returning data it should not or making a denial of service by executing resource-hungry operations.

Restricting the queries to only selects on a specific table might limit the impact, but you will probably still be able to cause a denial of service. And depending on where the results of the query will be used you might change the application behavior by returning unexpected results.

Steffen Ullrich
  • 184,332
  • 29
  • 363
  • 424
14

No, trivial example

EXEC ('SELECT COUNT(*) 
       FROM table 
       WHERE UserName =''''' + @UserName + ''''' AND Password = '''''+@Password+'''''')

Set @Password to a' OR 1=1;--

And your password gets bypassed

Akash
  • 733
  • 5
  • 15
  • Did you by any chance mean to have a closing single-quote after the password? Which, if I am not mistaken, should make it `Password = '''+@Password+'''')`. – user Apr 28 '15 at 08:36
  • @MichaelKjörling Yeah.. you're right... not sure of I've got the correct number of quotes now, but lets treat it as pseudocode :) – Akash Apr 28 '15 at 17:38
0

Not at all. SQL injection flaws are not at the SQL server but at the web application level, so if the web application is sensible to SQL injection, it will remain sensible no matter the access level to the database.

However, the principle of least privilege you mention makes that, even if someone manages to exploit an SQL injection on the web application, then he would only get read access on the database content, he will not be able to alter the database content or structure.

WhiteWinterWolf
  • 19,082
  • 4
  • 58
  • 104
  • 2
    More precisely a SQL injection flaw is a flaw in an ad hoc query -- that query is generally, but not always, built by the application. If your DB creates ad hoc queries, it too can be vulnerable to SQL injection. Either way, rad only will only prevent data modification, not data extraction or DoS. – jmoreno Apr 26 '15 at 17:27