0

I am creating a api for my web app which would be open source and users will be able to host it on there on websites, with this api users will be able to create tools for there own copy of the open source website and download and use other people tools. For the api I am thinking about letting the users query to the database using SQL for example if they want to get the name of something they would right a sql query using a JavaScript api wrapper and it would be sent to a PHP file to run the SQL query. For security purposes. the users are limited to query to a specific amount of tables and each time a key would be needed for querying

I want to know if this approach is secure as I don't want my users to be hackable like against the SQL injection.

user3761413
  • 3
  • 1

2 Answers2

1

Do not allow users to submit arbitrary SQL queries via the API.

If your API allows people to submit arbitrary SQL queries or expression, then it is not secure. Someone could write an SQL query that reads data he is not supposed to, or updates data in a way that destroys data or grants unforeseen privileges. Or just perpetrates a denial-of-service attack.

Here are some recommendations for writing a secure API with respect to risks of SQL injection vulnerabilities (there are other security issues with designing API's, to be sure).

  1. The best security is if the API allows users to submit only the value of the key, and then your PHP script would use a query parameter to bind that value into an SQL query that is hard-coded (with a parameter placeholder) in your PHP code.

  2. Next best security is if clients can choose from among several SQL queries, which are still hard-coded in your app. The client specifies some identifier for which of the pre-approved queries they want to use.

  3. The next best security if you need to allow API clients to design their own queries, then you should create a domain-specific language (DSL) so that your PHP app can validate that the client is submitting a query that you think is okay to run. Also see Writing Domain Specific Languages

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

feeding server-scripts with SQL-queries via JS-requests may just be the most insecure idea one can forge. If you want to allow custom data-queries via addons/extensions of any sort you will need to create a HTTP data-model which translates fixed requests into fixed sets of queries on your server. This is called "ORM" and a common technique - a few PHP examples are here : Good PHP ORM Library?

NEVER feed HTTP data input directly into SQL-calls on your server - such a system WILL get hacked over time.

Community
  • 1
  • 1
specializt
  • 1,913
  • 15
  • 26