How to check the entry of forbidden words in a MySQL database?

Question

Hypothetically, if I did not want the word "douche" to be anywhere in the username, and I have a table in my database with all the forbidden words ...

$q = "SELECT * FROM restrictions WHERE prohibited LIKE '%username%'";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_num_rows($r) !== 0)
{
//username is prohibited
echo "invalid";
}
else
{
...etc

The problem is that I don’t know how to execute a query that will receive partial matches (e.g. Jdoucher or douchebag4). I know that %% username% is obviously wrong. Does anyone know how to do this? Is it possible? Thank.

Answer 1

select *
    from restrictions 
    where locate(prohibited, @username) <> 0

Answer 2

You would do the opposite of the example you gave. First get all the banned keywords, then find each word in the username:

if (stripos($username, $word) !== FALSE) {
   // uhoh
}

Answer 3

, simshaun, , .

Of course, if your word database is too long, you look at processing time and high memory requirements.

Answer 4

You can accomplish this with regex .