1

I have a string, which stores my SQL code, and it looks like this:

'SELECT * FROM myTable WHERE myField LIKE '%2015' AND myOtherField = %d'

First question: How can I use formatting on myOtherField only? The code below does not seem to ignore the first % sign:

MyQuery.SQL.Add(Format(myString, myNumber));

Second question: How can I use formatting, when I need the first '%' sign for my SQL, but I want to format the date, too? Sadly, the following code does not work either. 

'SELECT * FROM myTable WHERE myField LIKE '%%d' AND myOtherField = %d'
Oh nooo
  • 478
  • 5
  • 19
  • 5
    Stop doing this and use parameters. You are safe here, but you are a short step away from SQL injection – David Heffernan Oct 07 '15 at 09:04
  • As for `Format`, the answer to your question can be found in the documentation: http://docwiki.embarcadero.com/Libraries/Seattle/en/System.SysUtils.Format – David Heffernan Oct 07 '15 at 09:05
  • 1
    @DavidHeffernan Not to mention that parameterized queries are simply more performant, often significantly so. – J... Oct 07 '15 at 09:16
  • 1
    We definitely need a "don't do this!" flag on stack overflow. This is a legitimate but wrong question - don't use string composition for possibly tainted values in SQL (I know %d is mostly safe, but the pattern is still problematic.) – Leonardo Herrera Oct 07 '15 at 10:06

2 Answers2

5
MyQuery.Sql.BeginUpdate;
MyQuery.Sql.Add('SELECT * FROM myTable');
MyQuery.Sql.Add('WHERE myField LIKE :MyField AND');
MyQuery.Sql.Add('myOtherField = :MyOtherField');
MyQuery.Sql.EndUpdate;

MyQuery.Parameters[0].Value := '%' + '2015';
MyQuery.Parameters[1].Value := intval;

I suggest this should be the boilerplate solution for all your queries; it requires very little extra effort to do and is more readable than fiddling with quotes and escape characters.

That is not to say the accepted answer is wrong, given the question.

Hugh Jones
  • 2,706
  • 19
  • 30
  • 1
    Alternatively, too, you can index your parameters by name. This is useful if you change the query later and the order of the parameters changes. ie: `MyQuery.Parameters.ParamByName('MyField').Value := '%2015'` – J... Oct 07 '15 at 09:32
  • 1
    J... is correct - `ParamByName` is particularly common when the sql string is separate from the code that is setting the parameter values. There is a small performance hit but it is safer. – Hugh Jones Oct 07 '15 at 09:35
  • Indeed, it can depend on the use case. I would code for clarity and maintanability first until performance becomes an issue, generally. – J... Oct 07 '15 at 09:39
  • @J... I am not sure I 100% agree with the addition of `BeginUpdate` `EndUpdate` in your edit. Does that not obfuscate the point a little? – Hugh Jones Oct 07 '15 at 09:39
  • Why not just do a direct assignment to MyQuery.Sql.Text instead of the .Add()s? – MartynA Oct 07 '15 at 10:35
  • @MartynA The `Add` method passes by reference - it saves copying and rebuilding intermediate strings, memory allocation, etc. – J... Oct 07 '15 at 11:46
  • @J...I suppose, but I've lost track of the number of times I've seen qs where the OP has constructed a Sql statement which contained syntatic or semantic error that weren't obvious because of Add()ing to the Sql in installments, as it were, whereas if they'd constructed a single string in the first place they could probably have spotted the error in the IDE evaluator themselves. – MartynA Oct 07 '15 at 12:12
  • @MartynA You can always add a watch on the `.Text` property... I find it cleaner than a sprawling concatenation, personally. – J... Oct 07 '15 at 13:04
  • My (unedited) answer set Sql.Text in one go. The point that I was trying to make was that parameters are easy to use. The `BeginUpdate` .. `Endupdate` with `Add()` looks more complex to my eye. – Hugh Jones Oct 07 '15 at 15:46
  • @HughJones Yes, but you're also suggesting to use this as boilerplate and, if you're going to do that, then you might as well make everything right, no? – J... Oct 07 '15 at 17:31
3

Add an extra % before the % sign you want to keep

Like this:

  Caption := Format('SELECT * FROM myTable WHERE myField LIKE ''%%2015'' AND myOtherField = %d', [4711]);

And same solution to question 2:

  Caption := Format('SELECT * FROM myTable WHERE myField LIKE ''%%%d'' AND myOtherField = %d', [4711, 4712]);
Jens Borrisholt
  • 6,174
  • 1
  • 33
  • 67
  • 1
    @Martyn Compare the string here, and that in the code, they are different – David Heffernan Oct 07 '15 at 09:02
  • @DavidHeffernan: Yes, I know, I was just meaning that the OP had obviously already tried %% (but presumably had tripped over not escaping the quotes). – MartynA Oct 07 '15 at 09:05
  • In my second code I was describing, that there is a '%' already, and the code is what I have tried to use, but did not work. This is the answer I was looking for, adding an extra % solves the problem. Thanks! – Oh nooo Oct 07 '15 at 09:08
  • 1
    @JensBorrisholt I will, but I have to wait one more minute. :) – Oh nooo Oct 07 '15 at 09:11
  • @ReyesJ. I agree you should accept this answer since it directly and correctly answers the question you asked. As others have noted, however, please take away from this that the above, although it now *works*, is still the *wrong* way to build an SQL query. Hugh's answer demonstrates the use of parameters to build a query - this is how you should actually solve your problem. – J... Oct 07 '15 at 09:20