Last time we created a simple, but not particularly useful, stored function. This time, we’ll create something a little more complicated and useful.
The MySQL server already has a LEFT() function, which returns a certain number of characters from the left-hand side of a string, but it doesn’t give any indication about whether any data was actually removed. A more useful function would truncate the string at the given point and append an indication if there was actually any more text.
Our new function: leftdots
We’ll jump straight in with the code:
DELIMITER // CREATE FUNCTION leftdots ( string TEXT, trimlength INT, dotstring TEXT ) RETURNS TEXT DETERMINISTIC SQL SECURITY INVOKER BEGIN IF ISNULL( trimlength ) THEN SET trimlength = 72; END IF; IF ISNULL( dotstring ) THEN SET dotstring = "..."; END IF; IF LENGTH( string ) <= trimlength THEN RETURN string; ELSE RETURN CONCAT( LEFT( string, trimlength ) , dotstring ); END IF; END; // DELIMITER ;mysql> SELECT leftdots('Short string', NULL, NULL); +--------------------------------------+ | leftdots('Short string', NULL, NULL) | +--------------------------------------+ | Short string + +--------------------------------------+ 1 row in set (0.01 sec)mysql> SELECT leftdots('Short string', 10, NULL); +------------------------------------+ | leftdots('Short string', 10, NULL) | +------------------------------------+ | Short stri... + +------------------------------------+ 1 row in set (0.01 sec)mysql> SELECT leftdots('Short string', 10, "... (more data follows)" ); +----------------------------------------------------------+ | leftdots('Short string', 10, "... (more data follows)" ) | +----------------------------------------------------------+ | Short stri... (more data follows) + +----------------------------------------------------------+ 1 row in set (0.01 sec)
As you can see, this one is a bit more advanced! The function properties are pretty straightforward – it’s a function called ‘leftdots’ that takes three parameters and returns a TEXT string.
You must pass exactly three parameters to this function, otherwise you’ll get an error. You cannot miss out parameters and rely on default values – the best you can do is pass a NULL and let the function replace them.
Also, in this example, we have ‘SQL SECURITY INVOKER’ – this tells the MySQL server that this particular function should be run with the privileges of the user who is calling it (the default is to run as the user who defined it). Although this is the simpler (and more secure) of the two cases, it’s not the default, so until you understand how running as a routine definer works it’s best to always include this.
The fun starts with the body declaration. Instead of a simple, one-line block of code, this function has several steps, and we wrap them in a BEGIN…END clause – this causes the MySQL server to consider them all to be a single statement. This is also the reason why we have to change the delimiter – if we hadn’t, the first semi-colon in our function code would have caused the MySQL server to start interpreting the incomplete declaration, leading to an error.
The first two commands are simple IF statements to put in default values if the function is passed NULLs for them. It’s important to note the the IF statement in stored functions looks different to IF statements you may put straight into an SQL statement. Here, if no “trimlength” value is passed, we default to 72 characters, and if no “dotstring” value is passed, we use three dots. These statements could also do sanity checks (like making sure the trimlength parameter is a positive integer, for example) and add security (like limiting trimlength to a maximum number of characters or percentage of string length) but for the purposes of this example I’ve kept it simple.
The next IF statement checks to see if the string we’re given as the first parameter is shorter than or equal to the trimlength – if it is, then we can pass the input value straight back unchanged. If the string is longer, we use the built-in LEFT() function to chop it off at the trimlength and CONCAT() to put the dotstring at the end.
We then end our code block with the END statement, use our delimiter to indicate to the MySQL server that we want to execute what we’ve entered so far, and set the delimiter back to the default.
Security
This kind of function is usually available at the application level, provided by the programming language, but this means that the entire data string needs to be transferred to the application, which raises two areas of concern – data transfer amounts (if the text being sliced is particularly large) and security (should the application really be able to access the entire text?)
If the routine is set to run as the invoker, it will be able to address the first concern (data transfer) but not the second (security) – the user running the function could just read the database table anyway. To address the security concern, it would have to run as the definer, but this creates a couple more issues – if the routine runs as a user that has access to all of the schemas, it would allow all users access to all of the database. If it set to run as a user with limited access then it may need to be defined multiple times to cover each of the possible access sets, causing duplicated code. In both cases, the routine would need to make sure that the length of the output text passed to it isn’t so large that it ends up returning the entire string anyway, which would completely negate any security benefits.
The best solution here is to have this routine run as the invoker, which would mean that it would still be of use without the potential for leaking any data. To gain access to data that a user wouldn’t normally be allowed to, another function would need to be created, running as an appropriate definer, that would check that the amount of data being requested is not unreasonable and call our function – the invoker of our function would be set to the definer of the wrapper function. This solution provides the best middle ground – any user can (potentially) use it to limit the amount of data transferred from any of the data fields they have access to, and that other functions specifically designed to provide access to data (by running as a privileged user) don’t contain too much duplicated code.
Next time we’ll cover creating and running MySQL stored routines as and with different definers.