Check if a data (URL or name - case insensitive) already exists in the database-CodePudding

I have a database insertion form with two fields: Site name, URL.

Before inserting into the database, I would like to check that the site in question does not exist (by checking if the name or the URL does not exist). The problem is that the name can be written differently (upper case, lower case, ...), as well as the URL.

What is the best solution? Here is my current code (not working):

<?php
// Database connection

$nameCompetitor = strtolower($_POST['nameSite']);
$urlCompetitor = parse_url('http://' . str_replace(array('https://', 'http://'), '', $_POST['url']), PHP_URL_HOST);

$reqCompetitorExist = "SELECT id_site, name_site, url FROM competitor_site WHERE LOWER(name_site) = \"".$nameCompetitor."\" OR url = \"".$urlCompetitor."\"";
$resCompetitorExist = mysqli_query($conn, $reqCompetitorExist) or die (mysqli_error($conn));
$competitorExist = mysqli_num_rows($resCompetitorExist);
$competitorData = mysqli_fetch_assoc($resCompetitorExist);
                                            
if ($competitorExist == 0) // Doesn't exist
{
    // INSERT OK
}
else // Exists
{
    echo "KO
        <ul>
            <li>ID:".$competitorData['id_site']."</li>
            <li>Name:".$competitorData['name_site']."</li>
            <li>URL:".$competitorData['url']."</li>
        </ul>";
}
?>

CodePudding user response：

you can change the "=" of the query by LIKE which already ignores the uppercase difference of the characters.

I also added SQL inject protection to your script.

<?php
// Database connection

$nameCompetitor = str_replace("'","",$_POST['nameSite']);
$urlCompetitor = parse_url('//' . str_replace(array('https://', 'http://'), '', str_replace("'","",$_POST['url'])), PHP_URL_HOST);

$reqCompetitorExist = "SELECT id_site, name_site, url FROM competitor_site WHERE name_site LIKE '".$nameCompetitor."' OR url LIKE '".$urlCompetitor."'";
$resCompetitorExist = mysqli_query($conn, $reqCompetitorExist) or die (mysqli_error($conn));
$competitorExist = mysqli_num_rows($resCompetitorExist);
$competitorData = mysqli_fetch_assoc($resCompetitorExist);
                                            
if ($competitorExist == 0) // Doesn't exist
{
    // INSERT OK
}
else // Exists
{
    echo "KO
        <ul>
            <li>ID:".$competitorData['id_site']."</li>
            <li>Name:".$competitorData['name_site']."</li>
            <li>URL:".$competitorData['url']."</li>
        </ul>";
}
?>