Using QuotedStr in SQL

I need to add another condition to my where cause below

SQL.Add('where (cmcl_bank_cleared is not null) AND ((cmcl_bank_cleared - check_date) >=:DaysParam)');

I need to also add

and (cmcl_bank_cleared <> to_date('01/01/2011', 'mm/dd/yyyy'))

the problem is the single 开发者_如何学Cquotes

can i do the following?

SQL.Add('where (cmcl_bank_cleared is not null) AND ');
SQL.Add('(cmcl_bank_cleared <> to_date(' + QuotedStr(01/01/2011) + ', ' + QuotedStr('mm/dd/yyyy') + ')');
SQL.Add('((cmcl_bank_cleared - check_date) >=:DaysParam)');

You can use QuotedStr to build queries, but it's not the best idea. If any of the input comes from a user, they could theoretically enter strange things that would end up having unwanted effects on your database. This is known as SQL Injection and is a serious security problem for a lot of websites.

The proper and safe way to insert values into the middle of a query like that is to use parameterized queries. Look up the documentation on the Params property of your dataset to learn how it works.

Yes, that will work fine. Remember that QuotedStr(S) escapes any quotes in S by doubling them up. Since you don't have any single quotes in your string you are fine.

I presume you mean QuotedStr('01/01/2011') rather than QuotedStr(01/01/2011). You've missed an AND too.

QuotedStr just makes the code more complicated for the way you're proposing to use it. Instead, since you're hard-coding the date in the SQL anyway, just use the ordinary Delphi syntax for putting apostrophes in string literals by doubling them:

SQL.Add('(cmcl_bank_cleared <> to_date(''01/01/2011'', ''mm/dd/yyyy'')) AND');

You'd use QuotedStr if you had variables (or constants) that contained strings that you wanted to incorporate into the SQL. For example:

const
  ExcludedDate = '01/01/2011';
  DateFormat = 'mm/dd/yyyy';

SQL.Add(Format('(cmcl_back_cleared <> to_date(%s, %s)) AND',
               [QuotedStr(ExcludedDate), QuotedStr(DateFormat)]));