Search words in html text in mysql field description without plain text

Question

I'm modifying code in dolibarr and i went through a problem with the search in desciption of products with WYSIG editor (recording html text). The search of description containing several words doesn't work after a
(maybe all tags) or at the very start of text, the first word does not return the description. Plus, "galvanisé" works with the "é" but not "d'options" with quote. see examples below :

I tried to use htmlentities and to include end or start of tags (see below).

the description :

Filtre standard avec tout un tas d'options 900x400x116
Nom 22
EN779-2002
acier galvanisé

for( $i=0 ; $i < sizeof($tabMots) ; $i++) {
    if($i == 0) {
        if(is_numeric($tabMots[$i])) 
            $sql.= ' WHERE (p.description LIKE \'% '.$tabMots[0].' %\' OR p.description LIKE \'%>'.$tabMots[0].' %\' OR p.description LIKE \'%>'.$tabMots[0].'<%\' OR p.description LIKE \'% '.$tabMots[0].'<%\' )';
        else
            $sql.= ' WHERE (p.description LIKE \'% '.$tabMots[0].'%\' OR p.description LIKE \'% '.htmlentities($tabMots[0]).'%\' OR p.description LIKE \'%>'.$tabMots[0].'%\' OR p.description LIKE \'%>'.htmlentities($tabMots[0]).'%\' )';
    } else {
        if(is_numeric($tabMots[$i])) {
            $sql.= ' AND (p.description LIKE \'% '.$tabMots[$i].' %\'  OR p.description LIKE \'%>'.$tabMots[$i].'%\' OR p.description LIKE \'%>'.$tabMots[$i].' %\' OR p.description LIKE \'%>'.$tabMots[$i].'<%\' OR p.description LIKE \'% '.$tabMots[$i].'<%\' )';
        } else {
            $sql.= ' AND (p.description LIKE \'% '.$tabMots[$i].'%\'  OR p.description LIKE \'% '.htmlentities($tabMots[$i]).'%\' OR p.description LIKE \'%>'.$tabMots[$i].'%\' OR p.description LIKE \'%>'.htmlentities($tabMots[$i]).'%\' )';

        }
    }
}

I would like to avoid to add a plain text field in order to not modify the dolibarr database. Thanks.

EDIT : The proposed Answer is no good because the problem is the text format HTML of the text description not criterias of search.

Possible duplicate of [php mysqli prepared statement LIKE](https://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like) — Dharman, Sep 12 '19 at 08:21

Pavel Janicek · Answer 1 · 2019-09-12T08:15:19.697

0

I think that your error will be in the fact that the ' sign in word d'options escapes the string in SQL engine.

$tabMots[0] = "d'options";
$sql.= ' WHERE (p.description LIKE \'% '.$tabMots[0].'%\'';
echo $sql;

Try the above online

The above will print out this:

WHERE (p.description LIKE '% d'options%'

Even from StackOverflow highlighting, you can see, what SQL engine gets from that.

I highly recommend to switch to prepared statements. Read about it here:

How can I prevent SQL injection in PHP?

Edit

How to switch to prepared statements? Example:

$param = $tabMots[$i];
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE CONCAT('%',?,'%') ");
$stmt->bind_param("s", $param);
$stmt->execute();

Copypasted from this answer: php mysqli prepared statement LIKE

edited Sep 12 '19 at 08:15

answered Sep 12 '19 at 07:14

Pavel Janicek

14,128
14
53
77

and the first word of a line what could it be ? – cilies38 Sep 12 '19 at 08:06
Edited. Hope that answers your question – Pavel Janicek Sep 12 '19 at 08:16
I know for prepared statement thanks. I thought i'd be useless cause no security problem. You think it could solve my first word problem too ? i'll give it a try. – cilies38 Sep 12 '19 at 08:32
easier no ? $resql = $db->prepare($sql); $resql = $db->execute($tabMots); – cilies38 Sep 12 '19 at 08:44
sorry, but with db of class DoliDB no prepare function in dolibarr 10. I think i have to copy plain text field and modify dolibarr behavior with product. – cilies38 Sep 12 '19 at 11:07
Can i use an other connection despite of the global $db (singleton) ? – cilies38 Sep 12 '19 at 11:25

cilies38 · Accepted Answer · 2019-09-16T06:53:45.717

For those who put -1 without thinking i want to say that prepared queries didn't solved my problem because it's formatting the word searched. Here the problem was the format of the description text and i made researches and saw that after inserting
htmlentities() keeps \n and other chars. However, i Don't know the char kept at the start of the text. Thanks for the help i've implemented nevertheless the prepared queries. So, you have to add this search for
inserted when you want a space between % and word '%\n'.$word.'%'. Here you have the root of the word with
before.

Search words in html text in mysql field description without plain text

2 Answers2