如何避免JavaFX桌面应用程序中的SQL注入和其他安全性故障 [英] How to avoid SQL injection and other security failure in JavaFX desktop application

问题描述

在JavaFX桌面应用程序中是否有任何方法可以避免SQL注入和其他安全性故障？如果是，我该怎么做？

Is there any method to avoid SQL injection and other security failure in JavaFX desktop application? If yes, how can I do it?

推荐答案

SQL注入攻击与最终用户故意发送到数据库的恶意语句有关，而JavaFX则是从用户角度看的前端。

SQL Injection attacks are related to malicious statements deliberately sent by the end user to the database, while JavaFX is the front-end from a user's point of view.

那就是说，我们假设你有一个登录界面来输入用户名和密码。你可以阻止用户输入以下句子之一而不是真正的用户名吗？

That said, let's assume you have a login screen to input user and password. Could you prevent the user to type one of the following sentences instead of their real user name?

DROP TABLE Users; --or
DELETE FROM Users WHERE 1=1;

您可以验证文本，查找某些关键字，例如 DROP ， INSERT ， UPDATE 或 DELETE 。但值得吗？也许确实如此，取决于用户尝试继续进行此类攻击的可能性。

You could validate the text looking for certain keywords like DROP, INSERT, UPDATE or DELETE. But is it worth? Maybe it does, depending on how probably is the users will try to go ahead with this kind of attacks.

然而，缓解和阻止SQL注入的最佳方法是从连接本身。简而言之，您将希望与具有较少必要操作权限的用户连接到数据库。通常的做法是创建一个专用用户来执行登录，例如，对 Users 表的只读访问权限，并且可能 INSERT 和 UPDATE 授予 Sessions 表（如果您有兴趣保留会话日志） ：

However the best way to mitigate and frustrate SQL injection begins from the connection itself. Tipically you'll want to connect to the database with users that have the less necessary privileges to operate. A common practice is to create a dedicate user to do the login for example, with read-only access to the Users table and maybe INSERT and UPDATE granted to a Sessions table (if you are interested in keep a sessions log of course):

CREATE USER 'login_user'@'%' IDENTIFIED BY 'password';
GRANT USAGE ON MyDataBase.* TO 'login_user'@'%';
GRANT SELECT ON Users TO 'login_user'@'%';
GRANT INSERT, UPDATE ON Sessions TO 'login_user'@'%';

注意： 该代码段基于MySQL但同样的概念也适用于其他RDBMS。

Note: the snippet is based on MySQL but the same concept applies to other RDBMS as well.

在这种情况下，如果最终用户成功将上述句子之一发送到数据库，那么db用户建立连接不会有足够的权限来执行这些句子，并将导致SQL异常。这同样适用于其他实体，只是为db用户提供使用敏感数据操作的较少权限。

In this scenario, if the end user succeeds in sending one of the above sentences to the database, the db user which was established the connection wont' have enough privileges to perform none of those sentences and will cause an SQL exception. The same applies to other entities as well, just provide a db user with the less privileges to operate with sensistive data.

此外，JDBC提供 PreparedStatement 接口，旨在通过使用占位符来构建声明。例如：

In addition, JDBC provides PreparedStatement interface which is intended to avoid SQL injection by using placeholders to build the statements. For example:

String sql = "SELECT * FROM Users WHERE username = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, userName);

userName 参数将包含在文字中在将语句发送到数据库之前，如果用户键入上述恶意句子之一，则不会产生任何影响。另外，如果要执行多个句子，则必须使用 addBatch（）和 executeBatch（）由开发人员控制，使其更安全。

The userName parameter will be wrapped into literals before sent the statement to the database so if the user types one of the above malicious sentences they won't have any effect. Plus, if you want to execute more than one sentence you have to use addBatch() and executeBatch() which is under developer's control, making it even safer.

这篇关于如何避免JavaFX桌面应用程序中的SQL注入和其他安全性故障的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！