6

Can they (malicious users) describe tables and get vital information? What about if I lock down the user to specific tables? I'm not saying I want sql injection, but I wonder about old code we have that is susceptible but the db user is locked down. Thank you.

EDIT: I understand what you are saying but if I have no response.write for the other data, how can they see it. The bringing to crawl and dos make sense, so do the others but how would they actually see the data?

johnny
  • 19,272
  • 52
  • 157
  • 259

7 Answers7

8
'); SELECT * FROM Users

Yes, you should lock them down to only the data (tables/views) they should actually be able to see, especially if it's publicly facing.

Scott Whitlock
  • 13,739
  • 7
  • 65
  • 114
  • Great cartoon: http://stackoverflow.com/questions/84556/whats-your-favorite-programmer-cartoon/84629#84629 – Scott Whitlock Aug 11 '09 at 22:08
  • I edited above. How would they actually see the users if I don't have anything that prints out those columns? – johnny Aug 11 '09 at 22:14
8

Someone could inject SQL to cause an authorization check to return the equivalent of true instead of false to get access to things that should be off-limits.

Or they could inject a join of a catalog table to itself 20 or 30 times to bring database performance to a crawl.

Or they could call a stored procedure that runs as a different database user that does modify data.

Harold L
  • 5,166
  • 28
  • 28
5

Only if you don't mind arbitrary users reading the entire database. For example, here's a simple, injectable login sequence:

select * from UserTable where userID = 'txtUserName.Text' and password = 'txtPassword.Text'

if(RowCount > 0) {
     // Logged in
}

I just have to log in with any username and password ' or 1 = 1 to log in as that user.

Aric TenEyck
  • 8,002
  • 1
  • 34
  • 48
3

Be very careful. I am assuming that you have removed drop table, alter table, create table, and truncate table, right?

Basically, with good SQL Injection, you should be able to change anything that is dependent on the database. This could be authorization, permissions, access to external systems, ...

Do you ever write data to disk that was retrieved from the database? In that case, they could upload an executable like perl and a perl file and then execute them to gain better access to your box.

You can also determine what the data is by leveraging a situation where a specific return value is expected. I.e. if the SQL returns true, execution continues, if not, execution stops. Then, you can use a binary search in your SQL. select count(*) where user_password > 'H'; If the count is > 0 it continues. Now, you can find the exact plain text password without requiring it to ever be printed on the screen.

Also, if your application is not hardened against SQL errors, there might be a case where they can inject an error in the SQL or in the SQL of the result and have the result display on the screen during the error handler. The first SQL statement collects a nice list of usernames and passwords. The second statement tries to leverage them in a SQL condition for which they are not appropriate. If the SQL statement is displayed in this error condition, ...

Jacob

TheJacobTaylor
  • 4,063
  • 1
  • 21
  • 23
  • Some of what we have is guarding against types of data entry. So we might have a date and if they did not enter a date it is rejected, but I still wonder if that can be exploited. – johnny Aug 11 '09 at 23:02
  • It would depend on the code. If you find a date that is followed by SQL injection attack, is it still considered a date? "12/01/1999'); drop table users" would be an interesting date to try. Make sure that you not only validate that you have a date, but that you also validate that you have nothing else. – TheJacobTaylor Aug 12 '09 at 18:53
2

I read this question and answers because I was in the process of creating a SQL tutorial website with a readonly user that would allow end users to run any SQL.

Obviously this is risky and I made several mistakes. Here is what I learnt in the first 24 hours (yes most of this is covered by other answers but this information is more actionable).

  • Do not allow access to your user table or system tables:

Postgres:

REVOKE ALL ON SCHEMA PG_CATALOG, PUBLIC, INFORMATION_SCHEMA FROM PUBLIC
  • Ensure your readonly user only has access to the tables you need in the schema you want:

Postgres:

GRANT USAGE ON SCHEMA X TO READ_ONLY_USER;
GRANT SELECT ON ALL TABLES IN SCHEMA X TO READ_ONLY_USER
  • Configure your database to drop long running queries

Postgres:

Set statement_timeout in the PG config file 
/etc/postgresql/(version)/main/postgresql.conf
  • Consider putting the sensitive information inside its own Schema

Postgres:

 GRANT USAGE ON SCHEMA MY_SCHEMA TO READ_ONLY_USER;

 GRANT SELECT ON ALL TABLES IN SCHEMA MY_SCHEMA TO READ_ONLY_USER;

 ALTER USER READ_ONLY_USER SET SEARCH_PATH TO MY_SCHEMA;
  • Take care to lock down any stored procedures and ensure they can not be run by the read only user

Edit: Note by completely removing access to system tables you no longer allow the user to make calls like cast(). So you may want to run this again to allow access:

GRANT USAGE ON SCHEMA PG_CATALOG to READ_ONLY_USER;
andy boot
  • 11,355
  • 3
  • 53
  • 66
  • 1
    Well the correct answer is just one word: Yes. It can be argued writing more than that is pointless. I imagine many people reading this question are in the position of having a database with a user who needs locked down readonly access. Which is why I added this answer. – andy boot Mar 02 '18 at 15:15
0

Yes, continue to worry about SQL injection. Malicious SQL statements are not just about writes.

Imagine as well if there were Linked Servers or the query was written to access cross-db resources. i.e. 

SELECT * from someServer.somePayrollDB.dbo.EmployeeSalary;
p.campbell
  • 98,673
  • 67
  • 256
  • 322
0

There was an Oracle bug that allowed you to crash the instance by calling a public (but undocumented) method with bad parameters.

WW.
  • 23,793
  • 13
  • 94
  • 121