Using cross platform sql in $DB->get_record_sql

Using cross platform sql in $DB->get_record_sql

by Luis de Vasconcelos -
Number of replies: 2
I'm upgrading an old Moodle plugin that was originally written for a Moodle 2.0 site and runs on a MS SQL Server 2016 database using the SQLSRV PHP driver.

One particular piece of code has me stumped:

$partyid = '123987';
$user_details = $DB->get_record_sql("
                SELECT * FROM mdl_user WHERE id = (SELECT TOP 1 userid FROM mdl_user_info_data
                JOIN mdl_user_info_field ON mdl_user_info_field.id=mdl_user_info_data.fieldid
                WHERE SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5',CONVERT(varchar,data))), 3, 32) = '$partyid' AND shortname = 'partyid')
                ");

The Moodle site has a custom profile field called "partyid" and the above code does a SELECT * query on the mdl_user table where the "partyid" custom profile field has a certain value. "partyid" is passed to Moodle as a MD5 hash of a short numeric number, 123987 in the above code.

The issue is that the WHERE part of the sub query uses SQL Server specific code, specifically the master.dbo.fn_varbintohexstr() and HashBytes() functions. How can the above code be written so that it works on all database platforms supported by Moodle?

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Using cross platform sql in $DB->get_record_sql

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Without really thinking about it, probably change

TOP 1 userid

to

MIN(userid)

Probably should also change it to use placeholders to put the values in the query, rather than string concatenation.
Average of ratings: Useful (1)
In reply to Luis de Vasconcelos

Re: Using cross platform sql in $DB->get_record_sql

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers
I'm also not familiar with SQL Server, but it looks like you're converting the data value into a substr of a hash in the query. I'd do that outside the query using PHP, perhaps something like:

$partyid = '123987';
$hashedpartyid = substr(md5($partyid), 3, 32);
$user_details = $DB->get_record_sql("
                SELECT * FROM mdl_user WHERE id = (SELECT TOP 1 userid FROM mdl_user_info_data
                JOIN mdl_user_info_field ON mdl_user_info_field.id=mdl_user_info_data.fieldid               
 WHERE data = :hashedpartyid AND shortname = 'partyid')", ["hashedpartyid" => $hashedpartyid]);


Average of ratings: Useful (2)