Convert plain password to prosody scram-sha-1

check_password.sql

DELIMITER $$
DROP FUNCTION IF EXISTS check_password;
CREATE FUNCTION check_password(U VARCHAR(255), P VARCHAR(255)) RETURNS BOOLEAN
BEGIN
        DECLARE auth SMALLINT DEFAULT 0;
        DECLARE return_value SMALLINT;
        SELECT COUNT(*) INTO auth FROM users WHERE username = U and password = ENCRYPT(P, password) LIMIT 1;
        IF auth > 0 THEN
                SELECT SCRAM("HOSTNAME", U, P) INTO return_value;
        END IF;
        RETURN auth;
END$$

scram-mysql.c

//gcc --std=c99 -fPIC -shared -o scram-mysql.so scram-mysql.c
#include <mysql/mysql.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>

long long scram(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) {
        char *server = (char *)args->args[0];
        char *user = (char *)args->args[1];
        char *password = (char *)args->args[2];
        char *exec_command = malloc (strlen(server)+strlen(user)+strlen(password) + 30);

        sprintf(exec_command, "scram.py '%s' '%s' '%s'", server, user, password);
        return system(exec_command);
}
my_bool scram_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
        if (args->arg_count < 3) {
                strncpy (message, "SCRAM(SERVER,USER,PASSWORD)", MYSQL_ERRMSG_SIZE);
                return 1;
        }
        for(int i = 0; i < args->arg_count; ++i) {
                if(args->arg_type[i] != STRING_RESULT) {
                        strncpy(message, "Just strings really.", MYSQL_ERRMSG_SIZE);
                        return 1;
                }
        }
        initid->maybe_null = 0;
        return 0;
}

scram.py

#!/usr/bin/python

from passlib.hash import scram
import sys
import uuid
import hmac
import sha
import MySQLdb

if len(sys.argv) > 3:
        server = sys.argv[1]
        user = sys.argv[2]
        password = sys.argv[3]

        db = MySQLdb.connect(host="localhost", user="prosody", passwd="secret", db="prosody")
        cur = db.cursor()
        exists = cur.execute("SELECT * FROM prosody WHERE user = '%s'" % user)
        if int(cur.rowcount) > 0:
                sys.exit(0)

        uuid_salt = str(uuid.uuid4())
        iteration_count = 4096

        sscram = scram.encrypt(password, rounds = iteration_count, salt = uuid_salt, algs = "SHA1")
        salted_password = scram.extract_digest_info(sscram, "sha1")[2]
        stored_key = sha.new(hmac.new(salted_password, "Client Key", sha).digest()).hexdigest()
        server_key = hmac.new(salted_password, "Server Key", sha).hexdigest()

        cur.execute("INSERT INTO prosody VALUES ('%s', '%s', 'accounts', 'iteration_count', 'number', %d);" % (server, user, iteration_count))
        cur.execute("INSERT INTO prosody VALUES ('%s', '%s', 'accounts', 'stored_key', 'string', '%s');" % (server, user, stored_key))
        cur.execute("INSERT INTO prosody VALUES ('%s', '%s', 'accounts', 'salt', 'string', '%s');" % (server,user, uuid_salt))
        cur.execute("INSERT INTO prosody VALUES ('%s', '%s', 'accounts', 'server_key', 'string', '%s');" % (server, user, server_key))
        db.commit()