getting and setting ENCODE / DECODE 'Column' function to AJax Sourced data

opusuk

I have an ajax sourced datatable with the use of the Editor plugin. The problem is i have a 'Password' field which needs to be encoded (with key value) upon new entry and decoded when editing the column. I have looked but can't find how to do this using the code.

allan

Hi,

Assuming you are using the Editor PHP libraries (?) you would use the getFormatter and setFormatter methods of the Field instance for the password field: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.Field.html .

These will effectively allow you to "transcode" between values, using whatever logic operation you wish.

Regards,
Allan

opusuk

Yes i thought that would be the best method, i did try this. I added extra functions to the format.php file along the lines of;

[code]
public static function decode_password( $val, $data, $opts ) {
if ( $val ) {
return DECODE( $val, 'secretkey' );
}
return '';
}

public static function encode_password( $val, $data, $opts ) {
if ( $val ) {
return ENCODE( $val, 'secretkey' );
}
return '';
}
[/code]

Am i right in saying returning the value here automatically inputs into the SQL query?

allan

That's correct yes. Whatever the setting function returns is what is inserted into the database. Likewise, whatever the getter returns is what is sent to the client for display.

It would be remiss of me to not note that you'll be transmitting unencrypted passwords over http(s). I'm sure you'll be well aware of this and the security implications of using encrypted passwords rather than hashed, but I have to point it out all the same :-)

Regards,
Allan

opusuk

Ok, how does this work based on MySQL's own Encode / Decode function. From what i can gather i can manipulate the results via PHP and output it again but if this requires MySQL code, how can i incorporate that?

allan

Ah I see. I'm afraid that the moment there is no way to execute SQL functions and use their results with the Editor PHP libraries. Providing this ability, though alias lookups, is something that I will look at adding to the libraries.

I'm afraid there isn't even a quick work around that I can see at the moment. You will probably need to write some custom queries in PHP.

Allan

opusuk

right ok, is this still not possible if i did my own server processing?

allan

Absolutely its possible with your own server-side code. I'm just not sure what that code is, so I'm not sure how best you would be able to integrate it I'm afraid. The Editor libraries are designed to cope with 80+% of cases, but they will never be as flexible as writing your own code!

Allan

opusuk

Hi Alan

I have been looking at the server_processing code on Datatables, it seems alright for the front end but in terms of the Add / Edit functionality, how can i incorporate this with Editor? Also how easy would it be to change the ajax source code to utilise my own sql queries once a record has been updated / inserted?

opusuk

Allan

Please ignore my previous comment. I have fixed part of my issue as you will see bwlow.

Editor Lib -> Format.php
[code]
public static function decode_password( $val, $data, $opts )
{
if ( $val )
{
$conn = mysqli_connect("", "", "", "");
if (mysqli_connect_errno())
{
return "Failed to connect to Database" . mysqli_connect_error();
}

$sql = "SELECT DECODE(password,'".$opts."') AS 'passDecode' FROM table WHERE ID = ? LIMIT 1";

if ($result = mysqli_query($conn,$sql))
{
$obj = mysqli_fetch_object($result);
return $obj->passDecode;
mysqli_free_result($result);
}
mysqli_close($conn);
}
return '';
}

public static function encode_password( $val, $data, $opts )
{
if ( $val )
{
$conn = mysqli_connect("", "", "", "");
if (mysqli_connect_errno())
{
return "Failed to connect to Database" . mysqli_connect_error();
}

$sql = "SELECT ENCODE('".$val."','".$opts."') AS 'passEncode'";

if ($result = mysqli_query($conn,$sql))
{
$obj = mysqli_fetch_object($result);
return $obj->passEncode;
mysqli_free_result($result);
}
mysqli_close($conn);
}
return '';
}
[/code]

Editor -> ajax script
[code]
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::inst( $db, 'table', 'ID' )
->fields(
Field::inst( 'username' ),
Field::inst( 'password' )
->getFormatter( 'Format::decode_password', 'key' )
->setFormatter( 'Format::encode_password', 'key' )
)
->process( $_POST )
->json();
[/code]

It's not pretty but seems to be working, Could you tell me how i would pass the ID of the record from the ajax script to the Format.php page, i need to get the ID field in-order to decode my password.

allan

Looks like a good method. It might not scale well to large number of rows because it is a lot of SQL queries, but a good interim step!

> Could you tell me how i would pass the ID of the record from the ajax script to the Format.php page, i need to get the ID field in-order to decode my password.

It will actually already be available in the $data (second parameter) that is passed into the formatter. That contains the data for the whole row, so `$row['DT_RowId']` will be the ID.

Allan