PHP preg_replace() is returning null when string includes a single quote but works otherwise

The code below is very simple. PHP uses POST to collect a string from a form, which I am then looking to trim and run a preg_replace function which will strip any special characters except a single quote or a hyphen. Bare in mind that the entire code works fine without the involvement of the quotes or hyphen in the regex expression.

preg_replace("/[^\w\s'-]/", '', $raw_lemurName);

Those clean variables are then inserted into a database. Apache/2.4.37. MariaDB.

When I make lemurName a string like "Diademed Sifaka<>!", it works, and returns 'Diademed Sifaka'.

When I make it a string including a single quote, however, like "Coquerel's Sifaka" the operation doesn't complete and no information is inserted.

I have tested the regex expression on its own and it works fine, it seems that when you begin to involve SQL and databases that it ceases to work.

Worth noting:

using phpMyAdmin. If I insert the string on there it works fine so my database can hold those values.
Tried using mysqli_real_escape_string() in various places, but have had no luck, perhaps doing it wrong.
Reading around, I think it has something to do with SQL not allowing strings with single quotes being inserted and that the server automatically escapes single quotes in the post method.

Any ideas?

Much appreciated.

$raw_lemurName          =    isset($_POST['lemurName']) ? $_POST['lemurName'] : null;

$raw_lemurLat           =    isset($_POST['lemurLat']) ? $_POST['lemurLat'] : null;

$raw_family             =    isset($_POST['family']) ? $_POST['family'] : null;

//the regex expression below seems to be messing something up

$c_lemurName            =    trim(preg_replace("/[^\w\s'-]/", '', $raw_lemurName));

$c_lemurLat             =    strtolower(trim(preg_replace('/[^\w\s]/', '', $raw_lemurLat))); 

$c_family               =    trim(preg_replace('/[^\w\s]/', '', $raw_family));

if (isset($_POST['submit'])) {

$query1 = "INSERT INTO `lemurs` (`id`, `lemur`, `latin`, `family`) VALUES (NULL, '$c_lemurName','$c_lemurLat','$c_family')";

$run_query = mysqli_query($connection, $query1);

if($run_query){
    echo "Data has been inserted";

} else {
    echo "Operation Unsuccessful";
}

    header("location:  index.php");

    return;

}

Solution

This is a standard SQL injection problem. The issue stems from the way you are getting these variables into your query:

$query1 = "INSERT INTO `lemurs` (`id`, `lemur`, `latin`, `family`) VALUES (NULL, '$c_lemurName','$c_lemurLat','$c_family')";

Think about exactly what is happening here, all you are doing is concatonating strings together, so if $c_lemurName is ' - then your SQL will become:

[...] VALUES (NULL, ''', '[...]

This actually really opens you up to what is called an "injection attack". Basically, a malicious user could set $c_family to something like... ');drop table lemurs;-- - you are now executing an insert statement, and then a drop table statement, with the rest of your SQL being a comment.

There are several ways to combat this, the most frequently advised way is to look into paramaterised queries - which for the mysqli library have to be done through prepared statements. There's an example of this on the PHP docs page.