Firedac select working with Firebird returns no records

2
votes

Hello I'm working with Firedac (Delphi Seattle) using Firebird (2.5) as a database, when I run this query using a TFDQuery, no records are returned:

SELECT ID FROM USERS WHERE PWD = 'êHÆ–!+'

The same query within a Database program as IbExpert return one record. Is there some parameter with Firedac components to configure that can solve this issue. Thanks.

1
delphifirebirdfirebird2.5firedac
What is your connection character set, what is the character set of the column? BTW: if this is actual production code, then for the love of security, rethink the way you authenticate users. Passwords should be hashed with a suitable password hashing algorithm, and you don't select them by password, but by username, and then you verify the hash. – Mark Rotteveel
The charset used for this field in Firebird is UTF8, also the CharacterSet used in Firedac Connection component is csUTF8. I'm agree about security suggestions, anyway this code is an example and there are some code previous to use this select, and another check after executing it. – A. Fornés
Have you tried with a parametrized query? – Uwe Raabe
Are you sure your Delphi code is actually unicode? – Mark Rotteveel
Thanks Uwe, your tip solved the issue. Anyway is strange because the same query works with other values, but with this fails. It seems that something qith that string is not good for Firedac. Regarding the Mark question I have assigned the CharacterSet parameter of the Firedac connection to csUTF8, I do not know if it is necessary to check any more params. – A. Fornés

1 Answers

2
votes

It's in the query string and it's the ! char. By default, query strings are preprocessed, and you must escape constant chars like !, &, :, ?, { or }, otherwise they are used as special chars.

Your best option is using parameters. That will (except other benefits) get rid of that ! char from the preprocessed command:

FDQuery.SQL.Text := 'SELECT ID FROM USERS WHERE PWD = :Password';
FDQuery.ParamByName('Password').AsString := 'êHÆ–!+';
FDQuery.Open;

Another option is escaping that constant char or disable macro preprocessor. For more information see the Special Character Processing topic.