MySQL UDF responds with garbage characters appended to returned string

Question

So I created a UDF that accepts 2 strings and concats them.

My UDF: // concat_kv.c

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <ctype.h>
#include <string.h>
#include <stdlib.h>

typedef unsigned long ulong;

my_bool concat_kv_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
    if(args->arg_count != 2 || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT) {
        strcpy(message, "concat_kv(): Requires 2 string parameters: Key - Value.");
        return 1;
    }
    return 0;
}

char *concat_kv(UDF_INIT *initid, UDF_ARGS *args, char *result, ulong *length, char *is_null, char *error) {
    char *key = (char*)calloc(strlen(args->args[0]), sizeof(char));
    char *value = (char*)calloc(strlen(args->args[1]), sizeof(char));
    char *res = (char *)calloc(strlen(args->args[0]) + strlen(args->args[1]) + 2, sizeof(char));
    int len = strlen(args->args[0]) + strlen(args->args[1]) + 2;
    key = args->args[0];
    value = args->args[1];
    strcat(res, key);
    strcat(res, " ");
    strcat(res, value);
    res[len-1] = '\0'; // Terminating character...
    return res;
}

void concat_kv_deinit(UDF_INIT *initid) {
}

Compiled the file as:

gcc $(mysql_config --cflags) -shared concat_kv.c -o concat_kv.so  

Moved the concat_kv.so file to /usr/lib/mysql/plugins/.

Created the function in mysql as:

CREATE FUNCTION concat_kv RETURNS STRING SONAME 'concat_kv.so';  

Then doing:

SELECT concat_kv("Aditya", "Singh") as conc;

Expected output:

| conc |
--------
| "Aditya Singh" |

But getting unexpected output as:

mysql> SELECT concat_kv("Aditya", "Singh") as conc;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| conc 
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aditya Singh            �T 
|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

I am getting something unprintable after the concatinated string. Some garbage values are appended after the string.

Answer 1

Your concat_kv function should look more like so:

char *concat_kv(UDF_INIT *initid, UDF_ARGS *args, char *result, ulong *length, char *is_null, char *error) {
    char *key = args->args[0];
    char *value = args->args[1];
    strcpy(result, key);
    strcat(result, " ");
    strcat(result, value);
    *length = strlen(result);
    return result;
}

Some remarks:

• the result param should be used for the result

• the length should be returned

• the length should be without terminating null character, see here: https://dev.mysql.com/doc/refman/8.0/en/udf-return-values.html

• key and value are just used from args and copied via strcpy/strcat into result

Demo

Dynamic Memory Allocation

As already pointed out in the comments, there is a danger of a buffer overrun if the size of the preallocated buffer is exceeded.

Memory Leak

If we were to dynamically allocate memory in concat_kv(), we would introduce a memory leak, since memory is requested every time the user-defined function is called, and is never released again.

The solution is to use the concat_kv_init() and concat_kv_deinit() functions, which are called by MySQL directly before and after the concat_kv() call.

Here a quote from the MySQL documentation:

MySQL passes a buffer to the xxx() function using the result parameter. This buffer is sufficiently long to hold 255 characters, which can be multibyte characters. The xxx() function can store the result in this buffer if it fits, in which case the return value should be a pointer to the buffer. If the function stores the result in a different buffer, it should return a pointer to that buffer.

If your string function does not use the supplied buffer (for example, if it needs to return a string longer than 255 characters), you must allocate the space for your own buffer with malloc() in your xxx_init() function or your xxx() function and free it in your xxx_deinit() function.

see https://dev.mysql.com/doc/refman/8.0/en/udf-return-values.html

Example with Dynamic Memory Allocation

So if we can't guarantee that the size of the result is large then the preallocated buffer, we need to allocate the memory in concat_kv_init() and free it in concat_kv_deinit().

my_bool concat_kv_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
    if(args->arg_count != 2 || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT) {
        strcpy(message, "concat_kv(): Requires 2 string parameters: Key - Value.");
        return 1;
    }
    ulong length = strlen(args->args[0]) + strlen(args->args[1]) + 2;
    initid->ptr = (char *)malloc(length);
    return 0;
}

char *concat_kv(UDF_INIT *initid, UDF_ARGS *args, char *result, ulong *length, char *is_null, char *error) {
    char *key = args->args[0];
    char *value = args->args[1];
    strcpy(initid->ptr, key);
    strcat(initid->ptr, " ");
    strcat(initid->ptr, value);
    *length = strlen(initid->ptr);
    return initid->ptr;
}

void concat_kv_deinit(UDF_INIT *initid) {
    free(initid->ptr);
    initid->ptr = NULL;
}