Board index » delphi » ADO bug - colon/word inside double quotes inside string being interpreted as param

ADO bug - colon/word inside double quotes inside string being interpreted as param

This is a bug in ADO:

1. New application.
2. Add a TADOCommand.
3. Build the ConnectionString appropriately.
4. Add a button.
5. Button code:

  with ADOCommand1 do
  begin
    s := 'update Customers set CompanyName = ' + chr(39) +
        'Antonio Moreno Taquera ":thisisabug"' + chr(39) +
        ' WHERE (CustomerID = ' + chr(39) + 'ANTOX' + chr(39) + ')';
    if InputQuery('','',s) then
    begin
      CommandText := s;
      Execute;
    end;
  end;
end;

Returns the error: "The application has improperly defined a parameter
object..."

6. Change code to:

  with ADOCommand1 do
  begin
    s := 'update Customers set CompanyName = ' + chr(39) +
        'Antonio Moreno Taquera "thisisabug"' + chr(39) +
        ' WHERE (CustomerID = ' + chr(39) + 'ANTOX' + chr(39) + ')';
    if InputQuery('','',s) then
    begin
      CommandText := s;
      Execute;
    end;
  end;
end;

IOW, remove the colon inside the double-quotes and run.

It runs fine.

7. Change code to:

  with ADOCommand1 do
  begin
    s := 'update Customers set CompanyName = ' + chr(39) +
        'Antonio Moreno Taquera :thisisabug' + chr(39) +
        ' WHERE (CustomerID = ' + chr(39) + 'ANTOX' + chr(39) + ')';
    if InputQuery('','',s) then
    begin
      CommandText := s;
      Execute;
    end;
  end;
end;

IOW, take out the inside double quotes and it runs fine.

Is there any workaround to this without getting into parameterized queries?
If no workaround to this, can you look to include it in your next set of
fixes, please?

Thanks

 

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Quote
Fred Dalgleish <fred.dal.nos...@home.com> wrote in message

news:81ev75$1591@forums.borland.com...

Quote
> Is there any workaround to this without getting into parameterized queries?
> If no workaround to this, can you look to include it in your next set of
> fixes, please?

Set the ParamCheck property to False.

Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Thanks Mark,

What happens where params are also involved?

Fred

Mark Edington (Borland) <meding...@nolunchmeat.com> wrote in message
news:81fiik$2hi15@forums.borland.com...

Quote
> Fred Dalgleish <fred.dal.nos...@home.com> wrote in message
> news:81ev75$1591@forums.borland.com...
> > Is there any workaround to this without getting into parameterized
queries?
> > If no workaround to this, can you look to include it in your next set of
> > fixes, please?

> Set the ParamCheck property to False.

> Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Hi Mark,

I've confirmed that the bug is still there if you also use params in the SQL
expression. Under that condition, you cannot set ParamCheck false, since
then the params never get evaluated and it throws a "cannot find object"
error. If ParamCheck is true, it checks the ":thisisabug" and attempts to
evaluate it as a parameter.

Can you point me to the source code section that does the parsing for this
and I'll fix it.

Thanks,

Fred

Mark Edington (Borland) <meding...@nolunchmeat.com> wrote in message
news:81fiik$2hi15@forums.borland.com...

Quote
> Fred Dalgleish <fred.dal.nos...@home.com> wrote in message
> news:81ev75$1591@forums.borland.com...
> > Is there any workaround to this without getting into parameterized
queries?
> > If no workaround to this, can you look to include it in your next set of
> > fixes, please?

> Set the ParamCheck property to False.

> Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Quote
Fred Dalgleish <fred.dal.nos...@home.com> wrote in message

news:81h5tt$ifj7@forums.borland.com...

Quote
> I've confirmed that the bug is still there if you also use params in the SQL
> expression. Under that condition, you cannot set ParamCheck false, since
> then the params never get evaluated and it throws a "cannot find object"
> error. If ParamCheck is true, it checks the ":thisisabug" and attempts to
> evaluate it as a parameter.

If you set ParamCheck to false then you need to create the parameter objects
manually.  However, if you use "?" style parameters and are working with SQL
Server then the parameter objects can still be created automatically by an
explicit call to Parameters.Refresh.

 > Can you point me to the source code section that does the parsing for this

Quote
> and I'll fix it.

I think it's TParameters.ParseSQL.  It's the same code that we use for BDE.  Not
being a full SQL parser it does have some problems.

Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Hi Mark,

I isolated the code and fixed it in both ADODB.PAS and DB.PAS. You need to
do a test while Literals is true to set/unset incidents of double quotes,
like this (although you would know better than me if my code is correct):

Declare a var: InDoubleQuotes as boolean;

In the ParseSQL procedure:

begin
  Result := SQL;
  Value := PChar(Result);
  if DoCreate then Clear;
  CurPos := Value;
  Literal := False;
  InDoubleQuotes := false; // << Check for double quotes inside strings
  EmbeddedLiteral := False;
  repeat
    while (CurPos^ in LeadBytes) do Inc(CurPos, 2);
    CurChar := CurPos^;
    if (CurChar = '"') then InDoubleQuotes := Literal and not
InDoubleQuotes; //
Open/close as needed but ensure it's inside single quoted strings
    if (CurChar = ':') and not Literal and not InDoubleQuotes and // Add the
InDoubleQuotes test
      ((CurPos + 1)^ <> ':') then
    begin
      StartPos := CurPos;
      while (CurChar <> #0) and (Literal or not NameDelimiter) do

What's your opinion?

Fred

Mark Edington (Borland) <meding...@nolunchmeat.com> wrote in message
news:81h9q4$ifn9@forums.borland.com...

Quote
> Fred Dalgleish <fred.dal.nos...@home.com> wrote in message
> news:81h5tt$ifj7@forums.borland.com...
> > I've confirmed that the bug is still there if you also use params in the
SQL
> > expression. Under that condition, you cannot set ParamCheck false, since
> > then the params never get evaluated and it throws a "cannot find object"
> > error. If ParamCheck is true, it checks the ":thisisabug" and attempts
to
> > evaluate it as a parameter.

> If you set ParamCheck to false then you need to create the parameter
objects
> manually.  However, if you use "?" style parameters and are working with
SQL
> Server then the parameter objects can still be created automatically by an
> explicit call to Parameters.Refresh.

>  > Can you point me to the source code section that does the parsing for
this
> > and I'll fix it.

> I think it's TParameters.ParseSQL.  It's the same code that we use for
BDE.  Not
> being a full SQL parser it does have some problems.

> Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Quote
Fred Dalgleish <fred.dal.nos...@home.com> wrote in message

news:81heaj$ifj20@forums.borland.com...

Quote
> What's your opinion?

Your specific example isn't really fixable given the current design.  The parsing
code handles the case of a colon following a double quotes specially to allow for
handling of parameter names with spaces.  Here is an example of a query that
contains a parameter with a space:

select * from customer c where c."First Name" = ":First Name"

Without the quotes the parameter would be interpreted as just the word "First".

When the colon appears in any position other than the first character following
the quote it is not treated as a parameter.

So the current behavior is by design.  If you need to have a literal containing a
quote in the first position you have to set ParamCheck to false and create the
parameters manually.

Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


That looks like a pretty strange param structure to me, but then I'm not as
knowledgeable about them as you are, Mark.

I can't get Delphi to take a param designated that way, Mark. Did you try it
with a TQuery?

I placed a TQuery on a form and put your select statement in the SQL
property, including double quotes, etc. then checked the TQuery's params.

The quotes and second word get dropped when I do it.

When done in code, the engine throws "Query1. Parameter 'First Name' not
found.

I'm not even sure how you'd code something like that. Here's what I tried:

  with Query1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from customer c where c."First Name" = ":First
Name"');
    ParamByName('"First Name"').AsString := 'me';
    Open;
  end;

But it didn't work...

Wouldn't you normally do this:

select * from customer c where c."First Name" = :FirstName

Then follow with a value assignment?

I really haven't ever seen your way of specifying a parameter, so it's
probably my lack of use of parameterized queries. Is yours the typical
approach?

Fred

Mark Edington (Borland) <meding...@nolunchmeat.com> wrote in message
news:81hgjm$lkl1@forums.borland.com...

Quote
> Fred Dalgleish <fred.dal.nos...@home.com> wrote in message
> news:81heaj$ifj20@forums.borland.com...

> > What's your opinion?

> Your specific example isn't really fixable given the current design.  The
parsing
> code handles the case of a colon following a double quotes specially to
allow for
> handling of parameter names with spaces.  Here is an example of a query
that
> contains a parameter with a space:

> select * from customer c where c."First Name" = ":First Name"

> Without the quotes the parameter would be interpreted as just the word
"First".

> When the colon appears in any position other than the first character
following
> the quote it is not treated as a parameter.

> So the current behavior is by design.  If you need to have a literal
containing a
> quote in the first position you have to set ParamCheck to false and create
the
> parameters manually.

> Mark

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


The other point, Mark, is that the test I'm attempting to make is to allow
double quoted colonated words to be handled as params, provided they are not
inside a literal. That's why I test "Literal or not InDoubleQuoted".

The bug in the Delphi code is that it doesn't distinguish between a true
parameter and a double-quoted word preceded with a colon, inside a
single-quoted string. IOW, the parser knows when it's inside or outside a
literal, but fails to consider the double quote inside the literal. Most
other products handle it correctly, in one way or another: ODBCExpress, VB
and Access handle it correctly.

Typically, you have to account for both single and double quotes when you
parse a SQL expression, and you have to monitor double quotes inside
single-quoted strings so that you don't interpret them as anything but
string values.

We've noted this problem in many memo field updates that contain quotes.
They end up getting replaced with question marks by one of the Delphi
parsers, again because it is not distinguishing between colonated words
outside and inside quoted strings. When I modified the DB.PAS file, those
problems went away.

I'm not saying I'm absolutely right here, but the logic that I'm using is
correct and is fairly widely acknowledged I think. For that reason,
something like my code should be incorporated into ADODB.PAS and DB.PAS, so
that the following very typical field value is not rejected:

INSERT INTO PATTERN
(Code, Description, Image)
VALUES(667,'AHREF="http://www.mydomaint.com">Here</A>','Mine')

This type of expression is being used more and more as developers use
database records to deliver web pages.

The way Delphi code is now, these get rejected since the
://www.mydomaint.com is read as a parameter, and it shouldn't be.

Fred

Re:ADO bug - colon/word inside double quotes inside string being interpreted as param


Quote
Fred Dalgleish <fred.dal.nos...@home.com> wrote in message

news:81hlff$lkk3@forums.borland.com...

Quote
> That looks like a pretty strange param structure to me, but then I'm not as
> knowledgeable about them as you are, Mark.

I was mistaken.  The actual syntax would be with the colon before the double quote
like so:

select * from table t where t."field name" = :"field Name"

So your point about the parser being broken is perfectly valid as I see it.  I'll
make sure the problem is logged.

Mark

Other Threads