SELECT with date field in WHERE clause fails via Ajax

423 Views Asked by At

A jquery builder (from http://querybuilder.js.org/ ) is used to let the user pick a date and further select data for a DataTables (datatables.net/ ) via a PHP function.

The DataTables and especially Ajax function looks like this:

var table = $(id).DataTable({
        serverSide: true,
        searching: true,
        processing: true,,
        ajax: {
            url: "controllers/myAjax.php",
            type: "POST",
            data: result
        }
});

The object passed as data is defined by queryBuilder and appended to my query string in the PHP script. To nail things down I pass the data as plain SQL (http://querybuilder.js.org/plugins.html#import-export). In my problem test case this is:

WHERE birthdate < '1990-01-01' 

This would result in the SELECT query:

SELECT * from table_1 WHERE birthdate < '1990-01-01' 

This query throws a MySQL error:

"[...] check the manual that corresponds to your MySQL 
server version for the right syntax to use near '\'1990-01-01\' "

Obviously the date doesn't get escaped correctly. But when I enter exactly this query to my MySQL workbench, the server executes and returns a correct set of results. Even more, the workbench doesn't care if I use single quote (') or double quote (").

Further, I tried to manually remove those escape chars using PHP str_replace. The function then returns values, but obviously interpreted as int and breaking other queries (like equal ID). Same goes for msqli.real-escape-string (http://php.net/manual/de/mysqli.real-escape-string.php).

Another approach I tried was to change the dataType of the Ajax function a little bit - but basically I am sending form-encoded data, so the default type for this should be fine?

So why does (only) the date field get escaped in a wrong manner? Is there any rather quick fix for this, before I have to write my own PHP functions for accessing the DB?

0

There are 0 best solutions below