javascript 使用JS执行MySQL查询及其涉及的安全问题

Use JS to execute MySQL queries and the security issues it involves

提问by

I've been searching around the internet for a way to define a query in JavaScript, pass that query to PHP. Let PHP set up a MySQL connection, execute the query and return the results json encoded.

我一直在互联网上寻找一种在 JavaScript 中定义查询的方法，将该查询传递给 PHP。让 PHP 建立一个 MySQL 连接，执行查询并返回 json 编码的结果。

However my concern is with the security of this method since users could tamper with the queries and do things you don't want them to do or request data you do not want them to see.

然而，我担心的是这种方法的安全性，因为用户可能会篡改查询并做您不希望他们做的事情或请求您不希望他们看到的数据。

Question

问题

In an application/plugin like this, what kind of security measures would you suggest to prevent users from requesting information I don't want them to?

在这样的应用程序/插件中，您建议采取什么样的安全措施来防止用户请求我不希望他们请求的信息？

Edit

编辑

The end result of my plugin will be something like

我的插件的最终结果将类似于

var data = Querier({
    table: "mytable",
    columns: {"column1", "column2", "column3"},
    where: "column2='blablabla'",
    limit: "10"
});

I'm going to let that function make an AJAX request and execute a query in PHP using the above data. I would like to know what security risks this throws up and how to prevent them.

我将让该函数发出 AJAX 请求并使用上述数据在 PHP 中执行查询。我想知道这会带来哪些安全风险以及如何防止它们。

采纳答案by Peter

Introducing easy JavaScript data access

So you want to rapidly prototype a really cool Web 2.0 JavaScript application, but you don't want to spend all your time writing the wiring code to get to the database? Traditionally, to get data all the way from the database to the front end, you need to write a class for each table in the database with all the create, read, update, and delete (CRUD) methods. Then you need to put some marshalling code atop that to provide an access layer to the front end. Then you put JavaScript libraries on top of that to access the back end. What a pain!

介绍简单的 JavaScript 数据访问

因此，您想快速构建一个非常酷的 Web 2.0 JavaScript 应用程序原型，但又不想将所有时间都花在编写连接代码以访问数据库上？传统上，要从数据库一路获取数据到前端，您需要为数据库中的每个表编写一个包含所有创建、读取、更新和删除 (CRUD) 方法的类。然后，您需要在其上放置一些编组代码，以便为前端提供访问层。然后您将 JavaScript 库放在其上以访问后端。多么痛苦！

Thisarticle presents an alternative method in which you use a single database class to wrap multiple database tables. A single driver script connects the front end to the back end, and another wrapper class on the front end gives you access to all the tables you need.

该文章介绍中，你使用一个数据库类来包装多个数据库表的替代方法。单个驱动程序脚本将前端连接到后端，前端的另一个包装类使您可以访问所需的所有表。

Example/Usage

示例/用法

// Sample functions to update authors
function updateAuthorsTable() {
    dbw.getAll( function(data) {
        $('#authors').html('<table id="authors"><tr><td>ID</td><td>Author</td></tr></table>');
        $(data).each( function( ind, author ) {
            $('#authors tr:last').after('<tr><td>'+author.id+'</td><td>'+author.name+'</td></tr>');
        });
    });
}

$(document).ready(function() {
    dbw = new DbWrapper();
    dbw.table = 'authors';

    updateAuthorsTable();

    $('#addbutton').click( function() {
        dbw.insertObject( { name: $('#authorname').val() },
        function(data) {
            updateAuthorsTable();
        });
    });
});

I think this is exactly what you're looking for. This way you won't have to build it yourself.

我认为这正是你要找的。这样您就不必自己构建它。

回答by T.J. Crowder

It's unclear from your question whether you're allowing usersto type queries that will be run against your database, or if your code running in the browser is doing it (e.g., not the user).

从您的问题中不清楚您是否允许用户键入将针对您的数据库运行的查询，或者您在浏览器中运行的代码是否正在执行此操作（例如，不是用户）。

If it's the user: You'd have to really trust them, since they can (and probably will) destroy your database.

如果是用户：您必须真正信任他们，因为他们可以（并且可能会）破坏您的数据库。

If it's your code running in the browser that's creating them: Don't do that.Instead, have client-side code send data to the server, and formulate the queries on the server using full precautions to prevent SQL Injection (parameterized queries, etc.).

如果是您在浏览器中运行的代码创建了它们：不要那样做。相反，让客户端代码将数据发送到服务器，并使用完全预防措施在服务器上制定查询以防止 SQL 注入（参数化查询等）。

---

---

Re your update:

重新更新：

I can see at least a couple issues:

我至少可以看到几个问题：

1. Here's a risk right here:

   where: "column2='blablabla'"
   

   Now, suppose I decide to get my hands on that before it gets sent to the server and change it to:

   where: "column2=');DROP TABLE Stuff; --"
   

   

   You can't send a complete WHEREclause to the server, because you can't trust it. This is the point of parameterized queries:

   Instead, specify the columns by name and on the PHP side, be sure you're doing correct handling of parameter values (more here).

   var data = Querier({
       table: "mytable",
       columns: {"column1", "column2", "column3"},
       where: {
          column2: {
            op:    '=',
            value: 'blablabla'
          }
       }
       limit: "10"
   });
   

   Now you can build your query without blindly trusting the text from the client; you'll need to do thorough validation of column names, operators, etc.

2. Exposing information about your scheme to the entire world is giving up information for free. Security is an onion, and one of the outer layers of that onion is obscurity. It's not remotely sufficient unto itself, but it's a starting point. So don't let your client code (and therefore anyone reading it) know what your table names and column names are. Consider using server-side name mapping, etc.

1. 这里有一个风险：

   where: "column2='blablabla'"
   

   现在，假设我决定在它被发送到服务器之前亲自动手并将其更改为：

   where: "column2=');DROP TABLE Stuff; --"
   

   

   您无法向WHERE服务器发送完整的子句，因为您无法信任它。这是参数化查询的要点：

   相反，按名称指定列，并在 PHP 端确保正确处理参数值（更多信息请点击此处）。

   var data = Querier({
       table: "mytable",
       columns: {"column1", "column2", "column3"},
       where: {
          column2: {
            op:    '=',
            value: 'blablabla'
          }
       }
       limit: "10"
   });
   

   现在您可以构建您的查询，而无需盲目相信来自客户端的文本；您需要对列名、运算符等进行彻底验证。

2. 向全世界公开有关您的计划的信息就是免费放弃信息。安全是一个洋葱，洋葱的外层之一是默默无闻。它本身还远远不够，但它是一个起点。所以不要让你的客户端代码（以及任何阅读它的人）知道你的表名和列名是什么。考虑使用服务器端名称映射等。

回答by Ismael Miguel

Depending on how you intend to do, you might have a hole bigger than the one made in this economy or no hole at all.

取决于你打算如何做，你可能会有一个比这个经济体更大的漏洞，或者根本没有漏洞。

If you are going to write the query on client-side, and send to php, I would create a user with only select, insert, deleteand update, without permissions to access any other database.
Ignore this if you use SQlite.
I advise against this!

如果你打算写一篇关于查询客户端，发送到PHP，我将创建只有一个用户select，insert，delete和update，没有访问权限的任何其他数据库。
如果您使用 SQlite，请忽略此项。
我建议不要这样做！

If you build the query on server-side, just stuff to the server the data you want!

如果您在服务器端构建查询，只需将您想要的数据填充到服务器！

I would change the code into something like this:

我会把代码改成这样：

var link = QuerierLink('sql.php');//filename to use for the query

var data = Querier('users',link);//locks access to only this table

data.select({
    columns: ['id','name','email'],
    where: [
        {id:{'>':5}},
        {name:{'like':'%david%'}}
    ],
    limit:10
});

Which, on server-side, would generate the query:

其中，在服务器端，将生成查询：

select `id`,`name`,`email` from `db.users` where `id`>5 and `name` like '%david%' limit 10

This would be a lot better to use.

这样使用起来会好很多。

With prepared statements, you use:

对于准备好的语句，您可以使用：

select `id`,`name`,`email` from `db.users` where `id`>:id and `name` like :name limit 10

Passing to PDO, pseudo-code:

传递给 PDO，伪代码：

$query='select `id`,`name`,`email` from `'.$database_name.'.users` where `id`>:id and `name` like :name limit 10';
$result=$PDO->exec($query,array(
         'id'=>5,
         'name'=>'%david%'
    )
);

This is the prefered way, since you have more control over what is passed.

这是首选方式，因为您可以更好地控制传递的内容。

Also, set the exactdatabase name along the name of the table, so you avoid users accessing stuff from other tables/databases.
Other databases include information_schema, which has every single piece of informationfrom your entire databasem, including user list and restrictions.
Ignore this for SQlite.

此外，沿着表的名称设置确切的数据库名称，这样您就可以避免用户访问其他表/数据库中的内容。
其他数据库包括information_schema，它包含整个数据库中的每一条信息，包括用户列表和限制。
对于 SQlite，请忽略这一点。

---

---

If you are going to use MySQL/MariaDB/other you should disable all read/write permissions.
You really don't want anyone writting files into your server! Specially into anylocation they wish.
The risk: They have a new puppy for the attackers to do what they wish! This is a massivehole.
Solution: Disable FILE privilegesor limit the access to a directory where you block external access using .htaccess, using the argument --secure_file_privor the system variable @@secure_file_priv.

如果您打算使用 MySQL/MariaDB/other，您应该禁用所有读/写权限。
您真的不希望任何人将文件写入您的服务器！专门进入他们希望的任何位置。
风险：他们有一只新的小狗供攻击者做他们想做的事！这是一个巨大的洞。
解决方案：禁用FILE 权限或限制对.htaccess使用参数--secure_file_priv或系统变量阻止外部访问的目录的访问@@secure_file_priv。

If you use SQlite, just create a .sqlite(3)file, based on a template file, for each client connecting. Then you delete the file when the user closes the connection or scrap every nminutes for files older than x time.
The risk: Filling your disk with .sqlitefiles.
Solution: Clear the files sooner or use a ramdisk with a cronjob.

如果您使用 SQlite，只需.sqlite(3)为每个连接的客户端创建一个基于模板文件的文件。然后，当用户关闭连接或每n分钟报废x 时间之前的文件时，您将删除该文件。
风险：用.sqlite文件填充磁盘。
解决方案：尽快清除文件或使用带有cron作业的 ramdisk 。

---

---

I've wanted to implement something like this a long ago and this was a good way to exercice my mind.
Maybe I'll implement it like this!

我很久以前就想实现这样的东西，这是锻炼我的思想的好方法。
也许我会像这样实现它！

回答by Adam

The more important thing is to be careful about the rights you grant to your MySQL user for this kind of operations.

更重要的是要注意您授予 MySQL 用户进行此类操作的权限。

For instance, you don't want them to DROP a database, nor executing such request:

例如，您不希望他们删除数据库，也不希望他们执行此类请求：

  LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE test FIELDS TERMINATED BY '\n';

You have to limit the operations enabled to this MySQL user, and the tables he has accessed.

您必须限制对这个 MySQL 用户启用的操作，以及他访问过的表。

Access to total database:

访问总数据库：

  grant select on database_name.*
      to 'user_name'@'localhost' identified by 'password';

Access to a table:

访问表：

  grant select on database_name.table_name
      to 'user_name'@'localhost' identified by 'password';

Then... what else... This should avoid unwanted SQL injection for updating/modifying tables or accessing other tables/databases, at least, as long as SELECT to a specific table/database is the only privillege you grant to this user.

然后......还有什么......这应该避免不需要的 SQL 注入来更新/修改表或访问其他表/数据库，至少，只要对特定表/数据库的 SELECT 是您授予该用户的唯一特权。

But it won't avoid an user to launch a silly bad-performance request which might require all your CPU.

但它不会避免用户发起一个愚蠢的性能不佳的请求，这可能需要你所有的 CPU。

var data = Querier({
    table: "mytable, mytable9, mytable11, mytable12",
    columns: {"mytable.column1", "count(distinct mytable11.column2)",
          "SUM(mytable9.column3)"},
    where: "column8 IN(SELECT column7 FROM mytable2
           WHERE column4 IN(SELECT column5 FROM mytable3)) ",
    limit: "500000"
});

You have to make some check on the data passed if you don't want your MySQL server possibly down.

如果您不希望您的 MySQL 服务器可能停机，您必须对传递的数据进行一些检查。