2

I am building a small API using Javalin & Exposed ORM. I'm trying to use the regex where condition but an exception is thrown because the SQL query performed due to my code seems to be incomplete (the pattern is missing).

fun getUsersByFilter(filter: String): List<User> {
    val regex = StringBuilder("/")
            .append(filter.toLowerCase())
            .append("/i")
            .toString()


    /**
     * The .regexp(someString) method take a string as argument (a pattern)
     */
    val users = transaction {
        User.find{ Users.pseudo.regexp(regex)  }.toList()
    }

    return users
}

  Position : 141. Statement(s): SELECT users.id, users.pseudo, users.email, users."password", users.admin, users.created_at, users.updated_at FROM users WHERE users.pseudo REGEXP ?
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERREUR: erreur de syntaxe sur ou près de « REGEXP »
  Position : 141
SQL: [SELECT users.id, users.pseudo, users.email, users."password", users.admin, users.created_at, users.updated_at FROM users WHERE users.pseudo REGEXP ?]

Can someone help me please?

Jolta
  • 2,620
  • 1
  • 29
  • 42
Yann Jesaispasquoi
  • 41
  • 6

2 Answers2

1

Ok i get it. The regex argument was correctly passed to the query (the question mark in the exception message was just there to point to the parameter...) even when using StringBuilder. The error was comming from PostgreSQL because the "REGEXP" function simply doesn't exist in PostgreSQL...

I finally use this code:

    fun getUsersByFilter(filter: String): List<User> {
        val regex = "%${filter.toLowerCase()}%"

        val users = transaction {
            User.find{ Users.pseudo.lowerCase().like(regex) }.toList()
        }

        return users
    }
Yann Jesaispasquoi
  • 41
  • 6
0

From your code, it looks as if you were trying to create a regex from the user input by concatenating /, the pattern and the /i delimiter + case insensitive flag.

It is wrong because:

  • In Kotlin (and Java) the regex patterns are specified via string literals, not regex literals as is usually the case in JavaScript, PHP, Perl, so you do not need to add / on both ends
  • Case insensitivity is set using either specific options (RegexOptions.IGNORE_CASE), or, easier, using inline modifiers, like (?i). Add it at the start and the whole regex will be case insensitive.
  • However, the user part, which should be matched literally, will throw an exception if it contains special regex metacharacters. You need to "quote" it and in Kotlin, you have Regex.fromLiteral method for that.

So, I suggest using

val regex = "(?i)" + Regex.fromLiteral(filter)
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • First of all thank you for the help. Unfortunately I still get the same error with this code ... I'm starting to wonder if the problem does not come from the Exposed API... Anyway i learned how to manipulate regex expressions in Kotlin and Java thanks to you – Yann Jesaispasquoi Jun 10 '19 at 12:44
  • @YannJesaispasquoi Well, please also check `val regex = Regex("(?i)" + Regex.fromLiteral(filter))`, compile the regex object. – Wiktor Stribiżew Jun 10 '19 at 12:46
  • This syntax is not working either, but i just figure it out why (answer just below). Anyway, thanks a lot for your time ! – Yann Jesaispasquoi Jun 10 '19 at 13:17
  • @YannJesaispasquoi Yeah, there you have the `~` operator. – Wiktor Stribiżew Jun 10 '19 at 13:21
  • 1
    Yes i just saw that few minutes ago, so as i thought, the error was comming from the ORM whor return "REGEXP" instead of the tilde when building the SQL query. – Yann Jesaispasquoi Jun 10 '19 at 13:29