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

Question

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.

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.

Answer 1

It's unclear from your question whether you're allowing users to 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.).

---

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 WHERE clause 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.

Answer 2

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, delete and update, without permissions to access any other database.
Ignore this if you use SQlite.
I advise against this!

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:

$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 exact database 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 information from your entire databasem, including user list and restrictions.
Ignore this for 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 any location they wish.
The risk: They have a new puppy for the attackers to do what they wish! This is a massive hole.
Solution: Disable FILE privileges or limit the access to a directory where you block external access using .htaccess, using the argument --secure_file_priv or the system variable @@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 n minutes for files older than x time.
The risk: Filling your disk with .sqlite files.
Solution: Clear the files sooner or use a ramdisk with a cron job.

---

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!

Answer 3

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

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.

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.

But it won't avoid an user to launch a silly bad-performance request which might require all your 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.

Answer 4

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!

This article 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.