Как экранировать запрос к базе данных SQLite?

Question

[Александр Вишнев]

Если в поисковой строке(Edit1) поставить символ, например ".", то появляется ошибка: "[FireDAC][Phys][SQLite] ERROR: fts5: syntax error near ".""
А так же, если стереть весь текст из Edit1, то ошибка: "[FireDAC][Phys][SQLite] ERROR: unknown special query:"

Как я понял надо экранировать текст запроса, но не совсем понимаю как это сделать.

procedure TForm1.FormCreate(Sender: TObject);
begin
FDQuery1.SQL.Text:=('CREATE VIRTUAL TABLE vdb USING fts5 ( text )');
FDQuery1.ExecSQL;
FDQuery1.SQL.Text:=('INSERT INTO vmdb VALUES ("Привет. Конь БарМалей, Корова?")');
FDQuery1.ExecSQL;
FDQuery1.SQL.Text:=('INSERT INTO vmdb VALUES ("Привет. Лось Ты лось: БорМалей ")');
FDQuery1.ExecSQL;
FDQuery1.SQL.Text:=('INSERT INTO vmdb VALUES ("Привет Бар ты Лось.")');
FDQuery1.ExecSQL;
end;

procedure TForm1.Edit1Typing(Sender: TObject);
var
 mr : string;
begin
     FDQuery1.SQL.Text:=('SELECT text FROM vdb WHERE text MATCH "'+Edit1.Text+'*" ORDER BY rank');
     FDQuery1.OpenOrExecute;
     mr:=(FDQuery1.FieldByName('text ').AsString);
     Memo1.Text := mr;
end;

Answer 1

[Александр Вишнев]

Проблема решилась удалением ненужных символов еще на этапе ввода текста, до того как он попадет в запрос.

procedure TForm1.sEdit2Typing(Sender: TObject);
var
s:string;
begin
S:=sEdit2.Text;   // Вводим запрос
S:=StringReplace(s, '~', '', [rfReplaceAll]);
S:=StringReplace(s, '`', '', [rfReplaceAll]);
S:=StringReplace(s, '!', '', [rfReplaceAll]);
S:=StringReplace(s, '@', '', [rfReplaceAll]);
S:=StringReplace(s, '"', '', [rfReplaceAll]);
S:=StringReplace(s, '#', '', [rfReplaceAll]);
S:=StringReplace(s, '№', '', [rfReplaceAll]);
S:=StringReplace(s, ';', '', [rfReplaceAll]);
S:=StringReplace(s, '$', '', [rfReplaceAll]);
S:=StringReplace(s, '%', '', [rfReplaceAll]);
S:=StringReplace(s, '^', '', [rfReplaceAll]);
S:=StringReplace(s, ':', '', [rfReplaceAll]);
S:=StringReplace(s, '?', '', [rfReplaceAll]);
S:=StringReplace(s, '&', '', [rfReplaceAll]);
S:=StringReplace(s, '*', '', [rfReplaceAll]);
S:=StringReplace(s, '(', '', [rfReplaceAll]);
S:=StringReplace(s, ')', '', [rfReplaceAll]);
S:=StringReplace(s, '-', '', [rfReplaceAll]);
S:=StringReplace(s, '_', '', [rfReplaceAll]);
S:=StringReplace(s, '+', '', [rfReplaceAll]);
S:=StringReplace(s, '=', '', [rfReplaceAll]);
S:=StringReplace(s, '[', '', [rfReplaceAll]);
S:=StringReplace(s, ']', '', [rfReplaceAll]);
S:=StringReplace(s, '{', '', [rfReplaceAll]);
S:=StringReplace(s, '}', '', [rfReplaceAll]);
S:=StringReplace(s, '|', '', [rfReplaceAll]);
S:=StringReplace(s, '>', '', [rfReplaceAll]);
S:=StringReplace(s, '<', '', [rfReplaceAll]);
S:=StringReplace(s, ',', '', [rfReplaceAll]);
S:=StringReplace(s, '\', '', [rfReplaceAll]);
S:=StringReplace(s, '/', '', [rfReplaceAll]);

sEdit1.Text:=S;  // "Чистый" запрос
end;

procedure TForm1.sEdit2ChangeTracking(Sender: TObject);  // procedure TForm1.sEdit1ChangeTracking(Sender: TObject);
var
 i : Integer;
 mr : string;
 empySQL, SQL : string;
 rs : string;
begin
  ListView1.Items.Clear;
    FDQuery2.Close;
    FDQuery2.Active:=True;
    FDQuery2.SQL.Clear;
    SQL := 'SELECT text FROM vdb WHERE text MATCH :search ORDER BY rank';
    empySQL := 'SELECT * FROM vdb';
    FDQuery2.SQL.Text := SQL;

    if
      (sEdit1.text='')
    or(sEdit1.text=' ')
    or(sEdit1.text='  ')
    or(sEdit1.text='   ')
    or(sEdit1.text='    ')
    or(sEdit1.text='     ')
    or(sEdit1.text='      ')
    or(sEdit1.text='       ')
    or(sEdit1.text='        ')
    or(sEdit1.text='         ')
    or(sEdit1.text='          ')
    or(sEdit1.text='           ')
    or(sEdit1.text='            ')
    or(sEdit1.text='             ')
    or(sEdit1.text='              ')
    or(sEdit1.text='               ')
    or(sEdit1.text='                ')
    or(sEdit1.text='                 ')
    or(sEdit1.text='                  ')
    or(sEdit1.text='                   ')
    or(sEdit1.text='                    ')
    or(sEdit1.text='                     ')
    or(sEdit1.text='                      ')
    or(sEdit1.text='                       ')
    or(sEdit1.text='                        ')
    or(sEdit1.text='                         ')
    or(sEdit1.text='                          ')
    or(sEdit1.text='                           ')
    or(sEdit1.text='                            ')
    or (pos('.', sEdit1.Text) > 0)
    or (pos(',', sEdit1.Text) > 0)
    or (pos('<', sEdit1.Text) > 0)
    or (pos('>', sEdit1.Text) > 0)
    or (pos('?', sEdit1.Text) > 0)
    or (pos(':', sEdit1.Text) > 0)
    or (pos(';', sEdit1.Text) > 0)
    or (pos('"', sEdit1.Text) > 0)
    or (pos('|', sEdit1.Text) > 0)
    or (pos('\', sEdit1.Text) > 0)
    or (pos('[', sEdit1.Text) > 0)
    or (pos(']', sEdit1.Text) > 0)
    or (pos('{', sEdit1.Text) > 0)
    or (pos('}', sEdit1.Text) > 0)
    or (pos('~', sEdit1.Text) > 0)
    or (pos('`', sEdit1.Text) > 0)
    or (pos('@', sEdit1.Text) > 0)
    or (pos('#', sEdit1.Text) > 0)
    or (pos('№', sEdit1.Text) > 0)
    or (pos('$', sEdit1.Text) > 0)
    or (pos('%', sEdit1.Text) > 0)
    or (pos('^', sEdit1.Text) > 0)
    or (pos('&', sEdit1.Text) > 0)
    or (pos('*', sEdit1.Text) > 0)
    or (pos('(', sEdit1.Text) > 0)
    or (pos(')', sEdit1.Text) > 0)
    or (pos('_', sEdit1.Text) > 0)
    or (pos('-', sEdit1.Text) > 0)
    or (pos('=', sEdit1.Text) > 0)
    or (pos('+', sEdit1.Text) > 0)
    or (pos('/', sEdit1.Text) > 0)
    then
    FDQuery2.SQL.Text := empySQL
       else
   FDQuery2.ParamByName('search').AsString := '^'+sEdit1.Text+'*';
   FDQuery2.Open;
     Memo1.BeginUpdate;
  for i:=0 to FDQuery2.RecordCount -1 do
     begin
     mr:=(FDQuery2.FieldByName('text').AsString);
	  Memo1.Text:=mr;
        FDQuery2.Next;
        end;
            Memo1.EndUpdate;
            end;

Answer 2

[Hemul GM]

Использовать параметры

Comments

[Александр Вишнев]

Попробовал. Не помогает.
Если select через like, то проблем нет, а вот через match на символы выдает ошибку.

[Hemul GM]

Александр Вишнев, значит не верно написал запрос

[Александр Вишнев]

Hemul GM, запрос написан верно. Но.. в запросе присутствует префикс-* и получается, что если перед префиксом будет символ(,.*?:%;№ и тд.) то запрос ломается.

Если экранировать текст запроса двойными кавычками, то тогда с введенными символами проблем нет. Но так же нет и того функционала который обеспечивался благодаря "*", так как запрос экранируется от "*".

SELECT text FROM vdb WHERE text MATCH '"Привет корова."' || '*' ORDER BY rank;

Без экранирования, с точкой в запросе:
Получаю > fts5: syntax error near "."

SELECT text FROM vdb WHERE text MATCH 'Привет корова.' || '*' ORDER BY rank;

Перелопатив кучу документации по FTS5 так и не нашел решения как экранировать, при этом не потеряв нужный функционал.

Нашел на Хабре статью:
https://habr.com/ru/company/simbirsoft/blog/534656/

Обратите внимание, что в FTS5 нельзя просто подставлять в MATCH любую строку. Если она будет содержать специальные символы, например, ‘?’ или ‘*’, то это будет считаться синтаксической ошибкой и приведет к SQLiteException. Чтобы избежать этого, заключаем строку в двойные кавычки, а если строка сама содержит двойные кавычки, дублируем их:

"\"${pattern.replace("\"","\"\"")}\""

Но не могу понять, как применить это для моего случая...

[Hemul GM]

Александр Вишнев, параметр ты как указываешь? Код покажи

[Александр Вишнев]

Hemul GM,

SQL := 'SELECT text FROM vdb WHERE text MATCH :search ORDER BY rank';
FDQuery2.SQL.Text := SQL
FDQuery2.ParamByName('search').AsString := '^'+sEdit1.Text+'*';
FDQuery2.Open;

[Александр Вишнев]

Hemul GM, но окончательный вариант кода с решением этой проблемы - полный костылинг... Считаю это недочетом именно SQLite, так как они могли сделать возможность такого экранирования на уровне запроса.