SELECT with date field in WHERE clause fails via Ajax <p>A jquery builder (from <a href="http://querybuilder.js.org/" rel="nofollow">http://querybuilder.js.org/</a> ) is used to let the user pick a date and further select data for a DataTables (datatables.net/ ) via a PHP function.</p> <p>The DataTables and especially Ajax function looks like this:</p> <pre><code>var table = $(id).DataTable({ serverSide: true, searching: true, processing: true,, ajax: { url: "controllers/myAjax.php", type: "POST", data: result } }); </code></pre> <p>The object passed as <em>data</em> 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 (<a href="http://querybuilder.js.org/plugins.html#import-export" rel="nofollow">http://querybuilder.js.org/plugins.html#import-export</a>). In my problem test case this is:</p> <pre><code>WHERE birthdate &lt; '1990-01-01' </code></pre> <p>This would result in the SELECT query:</p> <pre><code>SELECT * from table_1 WHERE birthdate &lt; '1990-01-01' </code></pre> <p>This query throws a MySQL error:</p> <pre><code>"[...] check the manual that corresponds to your MySQL server version for the right syntax to use near '\'1990-01-01\' " </code></pre> <p>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 ("). </p> <p>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 (<a href="http://php.net/manual/de/mysqli.real-escape-string.php" rel="nofollow">http://php.net/manual/de/mysqli.real-escape-string.php</a>). </p> <p>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? </p> <p>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? </p>