How to use LIKE statement query-exec/list/value?

Questions & Answers
1

How to use LIKE statement query-exec/list/value?
If it's like
(query-list db "SELECT * FROM Table WHERE name LIKE '%?%'" name) then '?' cannot be parsed. if format the statement by other function then there's a sql injection risk.

2

Here is an example using sqlite:

#lang racket/base

(require db
         racket/pretty)

(define (test)
  (define c (sqlite3-connect #:database 'memory #:mode 'create))

  (query-exec c "
CREATE TABLE IF NOT EXISTS example(
  id          INTEGER NOT NULL PRIMARY KEY,
  title       TEXT NOT NULL
);")

  (define (add-entry title)
    (query-exec c " INSERT INTO example (title) VALUES (?)" title))

  (add-entry "racket is a programming language")
  (add-entry "where can I buy a tennis racket?")
  (add-entry "do you like donuts?")
  (add-entry "the 10 best ways to cook ...")
  (add-entry "building your own #lang with racket")

  (define (find search)
    (pretty-print (query-rows c "SELECT * FROM example where title LIKE '%' || ? || '%'" search)))

  (find "racket"))

(module+ main
  (test))

This uses sqlite's concat operator || to add the % to the front and back of the string parameter.
Other sql dialects usually have a concat function that can be used like this concat('%', ?, '%')

2 Likes
3

Beware, though, that this is susceptible to LIKE-regexp injection, if the search string contains unescaped LIKE-regexp characters (such as %).

For sqlite, you should use the instr function instead if you want to check whether one string occurs inside of another.

3 Likes
4

Further information: in mysql CONCAT() is available