SQL commands tend to return the results in a fixed format that looks like a table. However SQL offer other ways to present and combine the data. In this post I will explore how to do this and the ways we can do SQL String Manipulation for MariaDB.
Table of Contents
Set-Up
Database Host
In a previous post I installed MariaDB and set up a database and discussed SQL Command Syntax. So I will use this Database and previously discussed commands in this post. My database is running with the following config.
| IP Address | 192.168.1.163 |
| Port | 3309 |
| User | root |
| Password | mypassword |
| Database Name | employees |
Manipulating Strings in SQL
substr
“substr” extracts part of a string from a string. For example if you needed to get all the characters from the string “The Green Car” you could use the below syntax,
select substr("The Green Car",1);
The number “1” here represents the starting point in the String you want to grab the string from. For example f you needed to get everything after the “The ” then you know you should start at the 4th character.
select substr("The Green Car",4);
Suppose you just want the last Three Characters? Then its just a matter of using a minus number
select substr("The Green Car",-3);
And if you just want to get a range of Characters, then you add an other number. First number is the starting point and second number is the amount of Characters to get.
select substr("The Green Car",4,6);
Length
Prints the length of a string.
select length("The Green Car");
It can also be used to get the length of the database name.
select length(database());
Delimiters
Delimiters can be really useful to make data readable and manageable in other apps if its exported. When using a statement with a “group_concat” it can be useful to delimited the data somehow. Lets take a look at the example given for the “group_concat” statement.
In this example there are two select statements joined together by a Union statement. The “group_concat” in the second “select” is grabbing an extra column (hire_date) that should not be allowed as the rules for union statements says the “the same number of columns must be in each select” so this is a fudge to get more data back. Typically seen in a sqli or blind sqli injection attack.
select first_name,last_name from employees where emp_no = '227474' union select first_name,group_concat(first_name,hire_date) from employees where emp_no = '242779';
We can easily add a delimiter between “first_name,hire_data”. We can add ” || ” between them separated by commas.
select first_name,last_name from employees where emp_no = '227474' union select first_name,group_concat(first_name, " || " , hire_date) from employees where emp_no = '242779';
Separators
When a “union” statement is used in conjunction with a “group_concat“, the data can be hard to read and messy.
However the “group_concat” has a separator option can be added between each row. For example you can make it a carriage return with the below code.
select first_name,last_name from employees where emp_no = '10010' union select NULL, group_concat(first_name," || ", last_name," || ",emp_no separator '\n') from employees where first_name = 'barry';
Wrap-Up
Although SQL String Manipulation would not normally come high in any SQL course, its is an essential skill to learn if you are interested in doing Capture the Flag challenges.
Support
I really enjoy making this content and if you would like to support the cost of keeping this site up and running, please make a purchase through one of my affiliate links.
Be First to Comment