Certain characters crash DB when used with SELECT FROM WHERE SIMILAR
TO ???
lazarus / 16-Databases

----------------------------------------------------------------------
WizardNo.7 · 2014-03-14 13:43

Hi all,

So with my DynamicUpdate procedure(whenever something is typed into
the tEdit component)

it runs a query to populate the dbgrid for every KeyUp event...

Code: [Select]CASE Rgpinputlanguage.ItemIndex OF
        0 : Begin
                Sqlquery1.Close;
                Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE
LATIN' +
                                      ' SIMILAR TO ''%' +
Edtliveinput.Text +
                                      '%''';
                Sqlquery1.Open;
            End;
        1 : Begin
                Sqlquery1.Close;
                Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE
AFRIKAANS'
                                      + ' SIMILAR TO ''%' +
Edtliveinput.Text +
                                      '%''';
                Sqlquery1.Open;
            End;
    End;

I've noticed that whenever I type a "(" ")" or "*" character I get a
DB Error(Attached images)

What to do?

----------------------------------------------------------------------
BigChimp · 2014-03-14 14:19

You can also try (air code untested)
Code: [Select] -- Pay BigChimp a lot of money!
or
Code: [Select]' DELETE * FROM dictionary
(make sure you have a backup of your db)
Don't allow SQL injection as you did ....
parametrized queries are the answer:

something like (air code, untested)
Code: [Select]CASE Rgpinputlanguage.ItemIndex OF
        0 : Begin
                Sqlquery1.Close;
                Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE
LATIN' +
                                      ' SIMILAR TO
''%:HIERDIEWOORD%''';
//assign value to parameter:
SQLQUery1.Params.ParamByName('HIERDIEWOORD').AsString:=Edtliveinput.Te
xt;
                Sqlquery1.Open;
            End;

See
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Select_query
_example
and
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Parameterized_queri
es

----------------------------------------------------------------------
mangakissa · 2014-03-14 14:28

Is SIMILAR not a equivant of LIKE?
I think you have problems with quotes.

Code: [Select]procedure Getdataset;
begin
  Sqlquery1.Close;
  CASE Rgpinputlanguage.ItemIndex OF
        0 : Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE
LATIN LIKE :paramid';
        1 : Sqlquery1.SQL.Text := 'SELECT * FROM DICTIONARY WHERE
AFRIKAANS  LIKE :paramid';
    End;
    Sqlquery1.params[0].AsString := '%' + Edtliveinput.Text + '%';
    Sqlquery1.Open;
end;

----------------------------------------------------------------------
BigChimp · 2014-03-14 15:12

@mangakissa: I'm curious
If they're equivalent, why change it to LIKE?
If not... well, actually the same

Also, why do you put the %s in the parameter itself? Esthetics? Ease
of reading? Some other reason?

----------------------------------------------------------------------
Leledumbo · 2014-03-14 15:46

QuoteIf they're equivalent, why change it to LIKE?
If not... well, actually the sameNo, they're not the same. See this.

----------------------------------------------------------------------
BigChimp · 2014-03-14 15:55

@Leledumbo: you're right - SIMILAR TO uses regexes - mangakissa is
probably right that LIKE will be good enough... and probably even
better...

----------------------------------------------------------------------
mangakissa · 2014-03-14 16:13

Quote from: BigChimp on March 14, 2014, 04:12:40 pm@mangakissa: I'm
curious
If they're equivalent, why change it to LIKE?
If not... well, actually the same
I thought they were the same, but Leledumbo shows different. LIKE is
as SQL92 standard function. In the case of WizardNo.7 it's a better
solution as far as I could see in his code.
QuoteAlso, why do you put the %s in the parameter itself? Esthetics?
Ease of reading? Some other reason?
- The query is more readable.
- Some parsers has difficulty with '%:paramid%'.
- It's better not to use extra quotes in your query.
- The result is easier to create.

----------------------------------------------------------------------
BigChimp · 2014-03-14 16:17

Quote from: mangakissa on March 14, 2014, 05:13:51 pmI thought they
were the same, but Leledumbo shows different. LIKE is as SQL92
standard function. In the case of WizardNo.7 it's a better solution as
far as I could see in his code.
Agreed.

Quote from: mangakissa on March 14, 2014, 05:13:51 pm- The query is
more readable.
- Some parsers has difficulty with '%:paramid%'.
- It's better not to use extra quotes in your query.
- The result is easier to create.
Ok, valid reasons...
Edit: actually, I think mangakissa is completely right: the other way
won't work (enclosing in quotes means sqldb never picks up the
parameter because it's a literal in SQL; leaving out the quotes is ok
but then you'd need to do SQL concatenation in your SQL query test...
mental trap I've walked into before

----------------------------------------------------------------------
BigChimp · 2014-03-15 14:00

Updated previous post