2

I am using Jquery Query Builder to create custom SQL Queries. And I am using Ajax to send the data to server and get the results.

But the problem is that this AJAX request is visible via Inspector Agents. And someone can easily change the query.

So i am trying to figure out ways to prevent sql injection. Here is my Javascript code:

// Define Query Option
var query_options = {
plugins: [
'bt-checkbox','sortable'
],
filters: [
{
    id: 'CITY',
    label: 'city',
    type: 'string',
    input: 'select',
    multiple: true,
    plugin: 'select2',
    plugin_config: {
      multiple: "multiple",
      data: []
    },
    operators: ['is_not_null', 'in', 'not_in', 'not_equal'],
    valueSetter: function(rule, value) {}
  }
]
}

// Get 'Cities' to use it in PHP File
$.get('custom/getCities', function(q_cities) {
  if (q_cities.length) {
    q_cities.forEach( function(element, index) {
      query_options.filters[0].plugin_config['data'].push(element['CITY']);
    });
  }
}, 'json'),


// Create Query
// @NOTE: This is where Sql injection can be made.
$('#btn-get').on('click', function() {
    var result = $('#builder-basic').queryBuilder('getSQL');
    var query = result.sql;
    if (!$.isEmptyObject(result)) {
    $.ajax
    ({ 
      url: 'custom/customquery',
      // This part is vulnerable to Sql injections.
      data: { query: query },
      type: 'post',
      dataType: "HTML",
      success: function(o)
      {
        if (o.length) {
            console.log(o);
        }
      }
    });
    }
});

And this is my PHP code:

// Server Side PHP script
public function customquery()
{
    if(isset($_POST['query'])){
        $query = $_POST['query'];
    }

    $qry = "SELECT * FROM MYTABLE WHERE " . $query;
    $result = $this->db->select($qry);
    echo json_encode($result);
}

I know that there are ways to prevent AJAX Sql injection But I couldn't find any answers to a specific Condition like Jquery Query Builder.

Thanks in advance.

Rüzgar
  • 947
  • 9
  • 20
  • You should first prevent the *Man in the middle* by TLS securing your connection with HTTPS. That way the packets sent between client and host server will be encrypted. Than on server side (PHP) take a look at PDO. – Roko C. Buljan Apr 28 '18 at 11:58
  • 2
    I would say that if you are executing whatever comes from javascript there is absolutely no way to stop me messing with the sql being sent from my browser – RiggsFolly Apr 28 '18 at 12:02
  • @RokoC.Buljan Thank you, Currently I am working on Xampp. But as soon as i go live, i am going to do that. – Rüzgar Apr 28 '18 at 12:07
  • @RiggsFolly Thank you, I thought so. But was curious if possible at client side. I think i am going to create a validation class at PHP so that users won't be able to exceed their scope, before sending the request to the server. – Rüzgar Apr 28 '18 at 12:12
  • And consider using prepared and bound queries to protect from [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's – RiggsFolly Apr 28 '18 at 12:14
  • you should use pdo for prevent sql injection. – Suman Apr 28 '18 at 12:23
  • There is no problem at the server side. Or so i think :) I am indeed using Prepared Statements with PDO. My database class handles that. `$this->db->select($srg);` Thx. – Rüzgar Apr 28 '18 at 12:25
  • @SumanDey Thank you. I am using that at the moment. – Rüzgar Apr 28 '18 at 12:25
  • I've never used that library but it seems it has two related but different features: compose rules in JSON format and export to SQL. The latter must be avoided here at any cost but the former is probably safe if used with sanity in mind: validate keys against a white list of column names, include values as prepared statement placeholders/parameters. – Álvaro González Apr 28 '18 at 14:06
  • @ÁlvaroGonzález Thanks for your comments. You're right. White-listing is what i had in mind. – Rüzgar Apr 28 '18 at 20:56

0 Answers0