PHP: Add quote() method for raw SQL access

GeorgeIoak

I see that this was added to the Editor in the 1.3.0 release (https://editor.datatables.net/download/1.3.0) but I haven't found any details on it.

Would I just take my editor instance and add something like this:

->quote("WHERE myUDF(" . $_GET['Lname'] . ") < 3")

What I'm looking for is the ability to use my UDF in a where clause

allan

Hi,

It is available in the API documentation for the PHP libraries here: http://editor.datatables.net/docs/1.4.0-beta/php/class-DataTables.Database.html#_quote

You would use it something like:

"WHERE myUDF(".$db->quote( $_GET['Lname'] ).") < 3"

Regards,
Allan

GeorgeIoak

Hi Allan:

Thanks for looking at this. I must be misunderstanding something because I'm getting an invalid JSON response when I try to use the new quote:

Fatal error</b>: Call to undefined method DataTables\Editor::quote()

The way I implemented is with this code:

<?php

/*
 * Editor server script for DB table allrecords
 * Automatically generated by http://editor.datatables.net/generator
 */

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;


// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'allrecords', 'id' )
    ->fields(
        Field::inst( 'conf_year' ),
        Field::inst( 'Fname' )
            ->validator( 'Validate::minLen', array( 'empty'=>false, 'min'=>2 ) ),
        Field::inst( 'Mname' ),
        Field::inst( 'Lname' )
            ->validator( 'Validate::minLen', array( 'empty'=>false, 'min'=>2 ) ),
        Field::inst( 'Delivery_Line_1' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'City_chk' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'State_chk' )
            ->validator( 'Validate::minLen', array( 'empty'=>false, 'min'=>2 ) ),
        Field::inst( 'ZIP_Code' )
            ->validator( 'Validate::minLen', array( 'empty'=>false, 'min'=>5 ) ),
        Field::inst( 'email' )
            ->validator( 'Validate::email', array( 'empty' => false ) ),
        Field::inst( 'Phone' )
    );
    
if ( isset($_GET['Lname']) && !empty($_GET['Lname']) ) { 
//    $editor->where( 'Lname', $_GET['Lname'] );
    $lookup = "WHERE damlev(`Lname`, '" . $_GET['Lname'] . "') < 2";
//  echo $lookup;
    $editor->quote( $lookup, $type = \PDO::PARAM_STR );
    print_r( $editor );
    }
    
if ( isset($_GET['conf_year']) && !empty($_GET['conf_year']) ) { 
    $editor->where( 'conf_year', (int)$_GET['conf_year'] );
    }
    
$editor
    ->process( $_POST )
    ->json();

GeorgeIoak

I'm sorry but I've tried several times to get the code formatted properly.

allan

Okay, the key thing to note here is that Editor does not currently support SQL functions at all. So using damlev() is no going to work regardless of how you construct the query.

This is actually something that I've been looking at making possible in 1.4 (currently in beta). Let me get back to you tomorrow when I've had a chance to experiment a bit.

Regards,
Allan

GeorgeIoak

OK, if I wasn't clear I am currently running the 1.4 beta

allan

What you can do is to use where() with an anonymous function which gives you access to the Query instance Editor is using. That then gives you the ability to specify additional options, including using a function in the field parameter.

Consider the simple case where we want to use abs() to get all records with an absolute value > 1 for the column sample:

    ->where( function ( $q ) {
        $q->where( 'abs(sample)', '1', '>' );
    } )

Applying that to your own damlev function:

    ->where( function ( $q ) use ( $db ) {
        $lname = $db->quote( $_GET['Lname'] );
        $q->where( "damlev(`Lname`, '" . $lname . "')", '2', '<' );
    } )

This requires the 1.4 libraries (for anyone else reading this!).

Regards,
Allan

GeorgeIoak

This is close. I fixed a couple of minor errors but it's still throwing a SQL syntax error, near the "')" but it looks OK to me. Yesterday I was trying to navigate through the code to find where I could log the actual SQL statement being sent to MySQL but I never figured it out. That would be helpful now to find the syntax error or perhaps something will pop out at you.

if ( isset($_GET['Lname']) && !empty($_GET['Lname']) ) { 
//    $editor->where( 'Lname', $_GET['Lname'] );
//  $lookup = "WHERE damlev(`Lname`, '" . $_GET['Lname'] . "') < 2";
//  echo $lookup;
//  $editor->quote( $lookup, $type = \PDO::PARAM_STR );
//  print_r( $editor );

    $editor->where( function ( $q ) use ( $db ) {
    $lname = $db->quote( $_GET['Lname'] );
    $q->where( "damlev(`Lname`, '" . $lname . "')", '2', '<' );
    } );
    }

GeorgeIoak

On a side note, why do I continue to have trouble getting code to display properly. Sometimes it works as expected but most times I lose all line breaks no matter how I paste the code in

allan

On a side note, why do I continue to have trouble getting code to display properly. Sometimes it works as expected but most times I lose all line breaks no matter how I paste the code in

Frustratingly the code highlighting doesn't "refresh" when you post a comment. You would need to reload the page for the syntax highlighter to kick in. I really need to get around to looking into fixing that...

Yesterday I was trying to navigate through the code to find where I could log the actual SQL statement being sent to MySQL but I never figured it out.

Take a look in Database/Driver/Myql/Query.php. In the _prepare method there should be a commented out file_put_contents() which I use for debugging. You could add your own debug there, or uncomment mine and see what is going on.

Allan

GeorgeIoak

Thanks for pointing that location, that really helps to see what's going on. That allowed me to see we were double quoting the last name so I made a minor modification:

$editor->where( function ( $q ) use ( $db ) {
    //$lname = $db->quote( $_GET['Lname'] );
    $q->where( "damlev(`Lname`, '" . $_GET['Lname'] . "')", '2', '<' );
    } );
    }

I figured why bother with one more step unless you can think of a reason to use $db->quote. Actually maybe I can already, if the last name includes a single quote my version would break.

You don't quite get the entire SQL statement in the _prepare method. I was seeing this:

...  FROM  `allrecords` WHERE damlev(`Lname`, 'Miller') < :where_0

which was enough for me to see the double quotes around Miller but I had to double check that :where_0 really was the 2 (I just ran some tests to see if the results looked reasonable).

allan

I figured why bother with one more step unless you can think of a reason to use $db->quote.

Yes - you open yourself to an SQL injection as you have it above! It would be trivial for someone to do: Lname = true';DROP ALL DATABASES;'...!

Allan

GeorgeIoak

Very good point. I hadn't thought of that because lname is actually populated from an autocomplete form on the page but that doesn't stop anyone from reading the AJAX call and changing it.