How to dynamically create a SQL SELECT request using Javascript then execute it with Ajax

Question

So here is my problem:

I need to get information from my database (MySQL) through filters applied with checkbox.

As a constraint I must do it in javascript then use Ajax to execute the request.

It would have been with radio input that would have been fine, but as it is checkbox I cannot determine in advance how many filter my user will have when getting the information they want.

I was thinking of creating a string that would be a concatenation of the filters I need but I'm not sure if it is good practice nor if it would be security breach :( (which is definitely BAD for that project)

here is an exemple of what I'd like to do:

[ ] a [x] b [ ] c

then my SQL request should be:

SELECT 'column' FROM 'table' WHERE 'id' = b

and in this case

[x] a [ ] b [x] c

then my SQL request should be:

SELECT 'column' FROM 'table' WHERE 'id' = a OR 'id' = c

I hope this does help you understand what I'm looking for.

And do not hesitate if you need more information!!

Thank you in advance =)

Answer 1

Don't build SQL queries on the client side. That means that your database will be open to running arbitrary queries by anyone. A malicious user could trick the browser into sending, for example, SELECT password FROM user (or worse if the DB user has write privileges).

Instead you should send your query parameters to your server as plain URL parameters. For example,

GET /query?a=true&b=false&c=true

Your server should extract the parameters, validate them (so that only known parameters, and only valid values for those parameters are accepted), and build a safe SQL query with properly escaped inputs using a proven query building library or SQL prepared statements.

How you do that depends on the platform that your server software uses. For example, in Node.js:

connection.query('SELECT * FROM table WHERE a = ? AND b = ? AND c = ?', [validatedA, validatedB, validatedC], function(err, results) {
  // ...
});

The mysql library takes care of correctly escaping the parameters to prevent SQL injection.

The MySQL library for your backend platform will have a similar function.

There are many other questions that touch on this subject. For example (both about PHP but the advice applies to any platform): How can I prevent SQL injection in PHP? and Best Way to Secure SQL Query in PHP.