Golang Gorm Escaping characters in Where clause not working

Question

I've got the following query which works just fine:

if err := rs.db.
        Preload("Artist").
        Preload("Genre").
        Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (? IN BOOLEAN MODE)", text).
        Find(&rs.Records).
        Error; err != nil {
        utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
        return errors.New(err.Error())
    }

It populates &rs.Records as expected.

But I need my query to be more flexible, so I tried to add some wildcards to it, so the raw query should look like the following:

SELECT * FROM records WHERE MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`)
AGAINST ('* TEXT YOU WANT TO SEARCH *' IN BOOLEAN MODE)

Notice the use of *. This translated to my Gorm query should be something like:

if err := rs.db.
        Preload("Artist").
        Preload("Genre").
        Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (*?* IN BOOLEAN MODE)", text).
        Find(&rs.Records).
        Error; err != nil {
        utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
        return errors.New(err.Error())
    }

It fails with the following error:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? IN BOOLEAN MODE)' at line 1

so clearly it's because I added *, so I tried to escape it:

if err := rs.db.
        Preload("Artist").
        Preload("Genre").
        Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (\\*?\\* IN BOOLEAN MODE)", text).
        Find(&rs.Records).
        Error; err != nil {
        utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
        return errors.New(err.Error())
    }

yet I'm getting the same error. I also tried adding escaped ' to it:

if err := rs.db.
        Preload("Artist").
        Preload("Genre").
        Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (\\'\\*?\\*\\' IN BOOLEAN MODE)", text).
        Find(&rs.Records).
        Error; err != nil {
        utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
        return errors.New(err.Error())
    }

But the result is the same.

Any ideas what am I doing wrong?

Thanks.

Answer 1

The argument to AGAINST() must be a quoted string, or a string parameter, or a constant string expression.

The following is not valid, because the * characters need to be inside a string value:

AGAINST (*?* IN BOOLEAN MODE)

The following is also not valid, because a parameter placeholder must not be inside the quotes:

AGAINST ('*?*' IN BOOLEAN MODE)

So your options are either create the expression as a concatenation of a string literal and parameter:

AGAINST (CONCAT('*', ?, '*') IN BOOLEAN MODE)

Or else concatenate the * characters into your pattern in your application, and pass that as the parameter value.

AGAINST (? IN BOOLEAN MODE)