如何安全地(间接)查询 android 中的 postgresql 数据库? [英] How can I securely (indirectly) query a postgresql database within android?
问题描述
我必须采用的当前解决方案使用 JDBC 并将数据库的用户/密码存储在 android 应用程序中.就我而言，这不是一个好的解决方案.我想在中间的网络服务器上实现一个映射层.
The current solution that I have to adopt uses JDBC and stores the user/password of the database inside the android app. That's as far as I'm concerned not a good solution. I would like to implement a mapping layer on the webserver in the middle.
是否有任何最佳实践或推荐策略?我应该使用 SOAP 或 JSON 还是完全不同的东西(因为它们在 Java 中实现得很好和/或易于使用)?是否有任何用于 postgresql <-> SOAP/JSON/PHP 中的任何映射工具，或者我是否需要自己编写这些脚本?
Is there any best practice or recommended strategy for this? Should I use SOAP or JSON or something completely different (because they're well implemented and/or easy to use in Java)? Are there any mapping tools for postgresql <-> SOAP/JSON/whatever in PHP or will I need to write these scripts by myself?
任何指针将不胜感激.
推荐答案
快速版:
使用在您控制的公共主机(可能但不一定是数据库主机)上运行的 Web 服务中间层.公开公共 Web 服务方法来完成您想要允许的有限工作，而不是别的.
Quick version:
Use a web service midlayer running on a public host you control (possibly but not necessarily the database host). Expose public web service methods to do the limited work you want to permit and nothing else.
我个人会使用 Java 应用服务器，例如 Apache Tomcat 或 JBoss AS 7 并且我会使用 JAX-RS 为我的应用程序生成一个很好的 REST 风格的 API.这就是我所熟悉的并且运行良好，但是您有很多选择，包括以下实现:
Personally I'd use a Java application server like Apache Tomcat or JBoss AS 7 and I'd write my web service methods using JAX-RS to produce a nice REST-style API for my app to use. That's what I'm familiar with and it works well, but you have lots of options including implementations of:
类似 REST 的 API(Java 的 JAX-RS impls Jersey 和 RESTEasy，以及其他各种语言工具)，它们使用 HTTP 请求并生成 JSON 或 XML 回复.
REST-like APIs (Java's JAX-RS impls Jersey and RESTEasy, various other langs tools) that use HTTP requests and produce JSON or XML replies.
SOAP 与 WSDL，经典的网络服务"层.在 Java 中使用 JAX-WS 和其他选项完成.大多数语言都有用于 SOAP+WSDL 的工具，但使用起来有点糟糕，尤其是在间歇性连接的设备(如手机)上.
SOAP with WSDL, the classic "web service" layer. In Java done with JAX-WS among other options. Most languages have tools for SOAP+WSDL but it's kind of crappy to work with especially on intermittently connected devices like mobiles.
XML-RPC 如果你喜欢痛苦
XML-RPC if you like pain
JBoss AS 7 快速入门列表；只需搜索JAX-RS".厨房水槽" 快速入门很有用，但如果您不熟悉 JBoss AS 7 和 Jave EE 6 的基础知识，这可能并不理想.为了 JAX-RS 的细节，您最好使用 Jersey 或 RESTEasy 教程，例如 this 或 这个.
There are some JAX-RS quickstarts on the JBoss AS 7 quickstarts list; just search for "JAX-RS". The "kitchen sink" quickstart is useful, though perhaps not ideal if you're not familiar with the basics of JBoss AS 7 and Jave EE 6. Fort the JAX-RS specifics you're better off with a Jersey or RESTEasy tutorial like this or this.
如果可能，请使用 HTTPs，如果访问不是公开的，请使用合适的 HTTP 身份验证方案，例如 HTTP 基本身份验证 over HTTPs.任何体面的 Web 服务实现都将提供身份验证选项或支持其运行平台的身份验证选项.避免在 Web 服务层实现自己的身份验证和用户管理的诱惑，你会把它搞砸；在已经编写和测试的 HTTP 层使用身份验证.这可能需要使用诸如 Apache 的 mod_auth_pgsql、JBoss AS 7 的 JDBC 安全领域等之类的东西.我认为不执行正确的每用户 HTTP 身份验证的唯一情况是我不需要出于安全原因将我的用户分开，我只关心它是我的应用程序访问服务器，即如果我的安全要求非常弱.在这种情况下，我会为整个应用程序使用固定的用户名/密码，如果 Android 支持，可能还会使用 X.509 客户端证书.
Use HTTPs if possible, and if access isn't to be public use a suitable HTTP authentication scheme like HTTP Basic auth over HTTPs. Any decent web services implementation will offer authentication options or support those of the platform on which it runs. Avoid the temptation to implement your own authentication and user management at the web services layer, you will screw it up; use the auth at the HTTP layer that's already written and tested. This may require the use of something like Apache's mod_auth_pgsql, JBoss AS 7's JDBC security realms, etc. The only case I'd consider not doing proper per-user HTTP auth is where I don't need to separate my users for security reasons, I only care that it's my app accessing the server, ie if my security requirements are quite weak. In this case I'd use a fixed username/password for the whole app and possibly an X.509 client certificate if Android supports them.
请记住，无论您如何保护事物，所有凭据要么为用户所知，要么可以从 .apk 中轻松提取，因此您仍然必须假设任何人都可以访问您的 Web 服务方法，而不仅仅是您的应用程序.相应地写下来.
Remember that no matter how you secure things, all credentials are either known to the user or can be extracted trivially from a .apk so you still have to assume anybody could access your web service methods, not just your app. Write them accordingly.
不要只是通过 Web 服务调用将 SQL 从您的应用程序发送到服务器，并将结果作为 JSON 返回.这是可怕的不安全，以及丑陋和笨重.为您希望应用程序能够执行的每个单独任务编写一个 Web 服务方法，并将 SQL 保留在服务器中.请记住使用参数化查询并注意其他 SQL 注入风险.这些 Web 服务方法可能使用一个或多个查询来生成单个回复——例如，您可能会收集客户"记录以及所有关联的地址"和联系方式"记录，然后将结果以一个很好的 JSON 对象返回到 Android 设备可以消费，省去无数缓慢不可靠的网络往返.
Do not just send SQL from your app over a web service call to the server and return the results as JSON. This is horrifyingly insecure, as well as ugly and clunky. Write a web service method for each individual task you want the app to be able to perform and keep the SQL in the server. Remember to use parameterised queries and be careful of other SQL injection risks. These web service methods may use one or more queries to produce a single reply - for example, you might collect a "Customer" record and all associated "Address" and "Contact" records then return the result in a nice JSON object the Android device can consume, saving numerous slow and unreliable network round trips.
无论您使用什么，请确保在后台工作线程中调用 Web 服务，不要阻塞用户界面.为超时和错误以及重试的需要做好准备.通过模拟间歇性连接丢失、高延迟和高丢包率来测试您的应用，并确保它保持可用.
No matter what you use, make sure to do your web service calls in a background worker thread and not to block the user interface. Be prepared for timeouts and errors, and for the need for retries. Test your app by simulating intermittent connection loss, high latency, and high rates of packet loss and make sure it remains usable.
这篇关于如何安全地(间接)查询 android 中的 postgresql 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
