How do I set a sql query parameter in Delphi that contains an '@' [closed]

Question

I am querying a database using an email address. The address is like fred.o'[email protected]

In Delphi that looks like fred.o''[email protected] as the apostrophe is escaped of course.

Let's say the query is...

'select * from table where (email = ''%s'')'

Now if I just replace %s the query fails as it seems to see the value as 2 strings 'fred.o' and '[email protected]', i.e. it doesn't recognise the escape.

If the query is...

'select * from table where email = :email' 

...then the parameter is set to the email address. It works if there is no apostrophe but doesn't match the data if the email address contains one.

[edited to remove incorrect data]

Any suggestions?

Answer 1

When using parameters, do not add the quotes or escape anything yourself.

This should work:

var
   email: String;
begin
  email := 'fred.o''[email protected]';
  MyQuery.SQL.Text := 'SELECT * FROM table WHERE email = :email';
  // Ensure that ParamCheck is enabled
  MyQuery.ParamCheck := True;
  // Explicitly set the type to string to ensure it's escaped
  // E.g. binary types are not escaped
  MyQuery.Params.ParamByName('email').DataType := ftString;
  MyQuery.Params.ParamByName('email').Value := email;
  MyQuery.Active := True;
end;

If it's not returning anything, check the actual values that are stored in the database. It may have not been escaped properly when it was inserted.