如果我的数据库用户是只读的，为什么我需要担心sql注入？

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.

他们（恶意用户）可以描述表格并获取重要信息吗？如果我将用户锁定到特定表格怎么样？我不是说我想要sql注入，但我想知道我们的旧代码是否容易受到影响但是db用户被锁定了。谢谢。

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?

编辑：我理解你在说什么，但如果我没有响应。写其他数据，他们怎么能看到它。带来爬行和dos是有意义的，其他人也是如此，但他们将如何实际看到数据？

7 个解决方案

#1

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.

有人可以注入SQL以使授权检查返回等效的true而不是false来访问应该禁止的事物。

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

或者，他们可以将目录表的连接注入其自身20或30次，以便将数据库性能提升到爬行状态。

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

或者，他们可以调用作为修改数据的不同数据库用户运行的存储过程。

#2

'); 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.

是的，您应该将它们锁定到它们实际上应该能够看到的数据（表/视图），特别是如果它是公开的。

#3

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.

我只需要使用任何用户名和密码登录'或1 = 1以该用户身份登录。

#4

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

要特别小心。我假设你已经删除了drop table，alter table，create table和truncate table，对吗？

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, ...

基本上，通过良好的SQL注入，您应该能够更改依赖于数据库的任何内容。这可能是授权，权限，访问外部系统，......

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.

您是否曾将数据写入从数据库中检索到的磁盘？在这种情况下，他们可以上传perl和perl文件之类的可执行文件，然后执行它们以便更好地访问您的盒子。

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.

您还可以通过利用期望特定返回值的情况来确定数据是什么。即如果SQL返回true，则继续执行，否则执行停止。然后，您可以在SQL中使用二进制搜索。 select count（*）其中user_password>'H';如果计数> 0则继续。现在，您可以找到确切的纯文本密码，而无需在屏幕上打印。

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, ...

此外，如果您的应用程序未针对SQL错误进行强化，则可能存在这样的情况，即它们可能在SQL或结果的SQL中注入错误，并在错误处理程序期间在屏幕上显示结果。第一个SQL语句收集一个很好的用户名和密码列表。第二个语句尝试在不适合的SQL条件中利用它们。如果在此错误情况下显示SQL语句，...

Jacob

雅各

#5

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.

我读了这个问题和答案，因为我正在创建一个SQL教程网站，其中包含一个允许最终用户运行任何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).

显然这是冒险的，我犯了几个错误。以下是我在前24小时内学到的内容（是的，其中大多数内容都包含在其他答案中，但此信息更具可操作性）。

• Do not allow access to your user table or system tables:
• 不允许访问您的用户表或系统表：

Postgres:

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:

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:

Postgres的：

Set statement_timeout in the PG config file 
/etc/postgresql/(version)/main/postgresql.conf

• Consider putting the sensitive information inside its own Schema
• 考虑将敏感信息放在自己的Schema中

Postgres:

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:

编辑：注意通过完全删除对系统表的访问，您不再允许用户进行类似cast（）的调用。因此，您可能希望再次运行此选项以允许访问：

GRANT USAGE ON SCHEMA PG_CATALOG to READ_ONLY_USER;

#6

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

是的，继续担心SQL注入。恶意SQL语句不只是写入。

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;

#7

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

有一个Oracle错误允许您通过调用带有错误参数的公共（但未记录）方法来使实例崩溃。