When merging Queries, Quotes appear in merged Query

Question

Ive been working on a Filter System for my searchfunction. There are a total of 4 Things to sort by (Group, First Name, Last Name, Company), so the amount of possible variantions make it impossible to just hardcode the query for each possible filter-version.

So I basically made a bunch of small queries which I fuze together at the end to give me the final query. But for some reason theres quotes in my query, which makes it fail.

So assuming I search for the term "vic" I get 2 results that are people called victor. One of them works at a Company the other doesnt. So I enter the other Victors company "LexCorp" (I use Batman Villains for dummy data) into the Company Filter. Now I should get the query:

SELECT Titel, Vorname, Nachname, Unternehmen, Gruppe, Geschlecht FROM mitglieder WHERE Unternehmen LIKE '%LexCorp%' AND Titel LIKE '%vic%' OR Unternehmen LIKE '%LexCorp%' AND Vorname LIKE '%vic%' OR Unternehmen LIKE '%LexCorp%' AND Nachname LIKE '%vic%'

But for whatever reason I get:

SELECT Titel, Vorname, Nachname, Unternehmen, Gruppe, Geschlecht FROM mitglieder WHERE'' Unternehmen LIKE '%LexCorp%'' AND Titel LIKE '%vic%''' OR ' Unternehmen LIKE '%LexCorp%'' AND Vorname LIKE '%vic%''' OR ' Unternehmen LIKE '%LexCorp%'' AND Nachname LIKE '%vic%''''

I am just totally unable to flag down whatever mistake I made in my code. Can anybody spot what I missed?

Heres my PHP:

    if(isset($_POST['search'])){                                                
    $searchq = $_POST['searchq'];                                                                                                               
    $gruppenfilter = $_POST['typfilter'];
    $vorname = $_POST['vorname'];
    $nachname = $_POST['nachname'];
    $unternehmen = $_POST['unternehmen'];
    $filcounter = 0;
    $TitelQuery = NULL;
    $VornameQuery = NULL;
    $NachnameQuery = NULL;
    $UnternehmenQuery = NULL;

    $searchquery = "SELECT Titel, Vorname, Nachname, Unternehmen, Gruppe, Geschlecht FROM mitglieder WHERE";


    $FilterArray = filterQuery($gruppenfilter, $vorname, $nachname, $unternehmen);
    $NoFilter = $FilterArray['NoFilter'];
    $FilterQuery = $FilterArray['FilterQuery'];
    $nachnameused = $FilterArray['nachnameused'];
    $vornameused = $FilterArray['vornameused'];
    $unternehmenused = $FilterArray['unternehmenused'];


    if($NoFilter == false){
        $TitelQuery = "'$FilterQuery' AND Titel LIKE '%$searchq%'";

        if($vornameused == false){
            $VornameQuery = " OR '$FilterQuery' AND Vorname LIKE '%$searchq%'";
        }
        if($nachnameused == false){
            $NachnameQuery = " OR '$FilterQuery' AND Nachname LIKE '%$searchq%'";
        }
        if($unternehmenused == false){
            $UnternehmenQuery = " OR '$FilterQuery' AND Unternehmen LIKE '%$searchq%'";
        }

        $searchquery .= "'$TitelQuery''$VornameQuery''$NachnameQuery''$UnternehmenQuery'";
    }
    else{
        $searchquery .= " Titel LIKE '%$searchq%' OR Vorname LIKE '%$searchq%' OR Nachname LIKE '%$searchq%' OR Unternehmen LIKE '%$searchq%'";    
    }



    echo $searchquery;

}
else{
    $searchquery = "SELECT Titel, Vorname, Nachname, Unternehmen, Gruppe, Geschlecht FROM mitglieder";
    $searchresult = filterTable($searchquery);
}

Heres the code of the function used in the code:

function filterQuery ($gruppenfilter, $vorname, $nachname, $unternehmen)
{
    $used = false;
    $vornameused = false;
    $nachnameused = false;
    $unternehmenused = false;

    $NoFilter = true;

    $FilterQuery = "";

    if(! empty($gruppenfilter)){
        $FilterQuery .= " Gruppe LIKE '%$gruppenfilter%'";
        $NoFilter = false;
        $used = true; 
    }
    if(! empty($vorname)){
        if ($used == true){
            $FilterQuery .= " AND Vorname LIKE '%$vorname%'";    
        }
        else{
            $FilterQuery .= " Vorname LIKE '%$vorname%'";
            $used = true;
            $NoFilter = false;
        }

        $vornameused = true;
    }
    if(! empty($nachname)){
        if ($used == true){
            $FilterQuery .= " AND Nachname LIKE '%$nachname%'";
        }
        else{
            $FilterQuery .= " Nachname LIKE '%$nachname%'";
            $used = true;
            $NoFilter = false;
        }

        $nachnameused = true;
    }
    if(! empty($unternehmen)){
        if ($used == true){
            $FilterQuery .= " AND Unternehmen LIKE '%$unternehmen%'";
        }
        else{
            $FilterQuery .= " Unternehmen LIKE '%$unternehmen%'";
            $NoFilter = false;
        }

        $unternehmenused = true;
    }

    $FilterArray['NoFilter'] = $NoFilter;
    $FilterArray['FilterQuery'] = $FilterQuery;
    $FilterArray['nachnameused'] = $nachnameused;
    $FilterArray['vornameused'] = $vornameused;
    $FilterArray['unternehmenused'] = $unternehmenused;

    return $FilterArray;
}

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Sep 04 '18 at 12:50
Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Sep 04 '18 at 12:50
@JayBlanchard Thanks! Ill take a look! But do you know how I could get it to work right now? The site still has a ways to go before being live and I have a list of security risks and ugly code, that Ill worry about as soon as I get the features running in the first place. — w00ds98, Sep 04 '18 at 12:54
Well if you use stuff like `$TitelQuery = "'$FilterQuery' AND …"`, then what did you _expect_ to happen? You introduced `'` before and after the value here. — misorude, Sep 04 '18 at 12:54
If you use prepared statements you'll never have to worried about quotes in queries ever again. — Jay Blanchard, Sep 04 '18 at 12:55
The error lies within your concatenation of the queries. You'd have to restructure it a bit and see where the single quotes are positioned in each concatenation. That's at least what I see *instantly* from skimming the code. — Martin, Sep 04 '18 at 12:58
@misorude I assumed thats how you use variables in SQL? By adding single quotes around the variable? — w00ds98, Sep 04 '18 at 12:59
_“I assumed thats how you use variables in SQL?”_ - this isn’t SQL, you are handling string values in PHP here at this point. — misorude, Sep 04 '18 at 13:03

score 1 · Accepted Answer · answered Sep 04 '18 at 13:01

1

In your php query you doing something like this and that causes the issue.

$searchquery .= "'$TitelQuery''$VornameQuery''$NachnameQuery''$UnternehmenQuery'";

you can concatenate php string as follows.

$searchquery .= "$TitelQuery $VornameQuery $NachnameQuery $UnternehmenQuery";

that will remove all extra quotes.

answered Sep 04 '18 at 13:01

Lahiru Madusanka

270
2
13

Thanks that did it :) – w00ds98 Sep 04 '18 at 13:03

score 1 · Answer 2 · answered Sep 04 '18 at 13:01

I agree with the comments that say you should use prepared statements, but for the issue you're having, in this part:

if($NoFilter == false){
        $TitelQuery = "'$FilterQuery' AND Titel LIKE '%$searchq%'";

        if($vornameused == false){
            $VornameQuery = " OR '$FilterQuery' AND Vorname LIKE '%$searchq%'";
        }
        if($nachnameused == false){
            $NachnameQuery = " OR '$FilterQuery' AND Nachname LIKE '%$searchq%'";
        }
        if($unternehmenused == false){
            $UnternehmenQuery = " OR '$FilterQuery' AND Unternehmen LIKE '%$searchq%'";
        }

You want to remove the quotes around $FilterQuery

The reason you have single quotes around variables in other parts of your sql query is that for the SQL to work, you need the quotes because the variables represent strings. In the case of $FilterQuery it actually represents parts of an SQL query, not strings within it.

--edit--

The answer provided by Lahiru Madusanka is also right - a problem for the same reason - you're putting quotes around SQL query, not strings

As you mentioned another user also gave a correct answer, since he was earlier I marked his answer as the right one, but Thank you for the help aswell! — w00ds98, Sep 04 '18 at 13:09

When merging Queries, Quotes appear in merged Query

2 Answers2