[Phpmyadmin-devel] Re: Big problem with "PMA_sqlFormat()"

[Robin Johnson]

On Sun, 7 Jul 2002, Rabus wrote:
> ----- Original Message -----
> From: "Loïc" <loic-div at ifrance.com>
> > That's really annoying but this new function has some big
> > bugs. For example, just tries to enter this value for a varchar
> > field: "this is a delete test". You'll see it is changed to "this is
> > a DELETE test" (case is changed) :((
>
> The main problem is that PMA_sqlFormat() does not make a difference between
> MySQL expressions and quoted strings. I already noticed that when fixing bug
> #575867.
>
> Robin, what about your new parser? Would it solve this problem?
Yes, it would totally fix it.
My parser does an extreme breakdown of the SQL query.
Here are the things recognized indiviually by my parser:
comments
 - # style
 - /* */ style (ANSI-SQL compliant)
digit
 - hexadecimal
 - floating point
 - integer
alphanumeric
 - reserved words
 - functions
 - column attributes
 - column types
 - identifiers
 - SQL variables
strings (escaped with ' " `)
 - single
 - double
 - backtick
punctucation
 - open bracket (
 - close bracket )
 - item seperator ,
 - identifier qualifier .
 - end of query ;
 - (all other punct is grouped together, but I can break it out into the
    ones for comparision and math if needed)

My parser even correctly handles escaped strings (to the EXTREME escaping
even)

Select "F\'oo \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\";
Select "F\'oo \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"";

Additionally, my current format function does not destroy the query at all
(apart from newlines inside a string, but that is a configuration option,
because some people will want it to behave one way, while others will want
it to behave the other way). I have used CSS to do all of the uppercase,
coloring and indenting.

One thing my code is majorly missing at the moment, is designs on what to
do when there is an error in the query. At the moment I just use
trigger_error() and return NULL from the function.

But my error system has hooks for all of the following errors:
Unmatched quotes
reserved word used as identifer (this is still very rough and WIP)
invalid literal value

I think I'm going to leave the error handling hooks in place for now, just
with a NULL return, and the rest of the error handling can be implemented
later.

I had updated my parser to deal with the problem of identifiers starting
with a digit as I listed before, but it's made my code 20% slower. I'm
trying to get it back down to the speed I had it before now.

I did find one other minor bug that I'm crunching now:
SELECT (2+3) >=1;
is parsed as:
SELECT ( 2 + 3) >= 1;
but:

SELECT (2+3)>=1;
is parsed as:
SELECT ( 2 + 3 ) > = 1;

I need to make the punctuation sections smarter so that they can seperate
out the seperate elements correctly, namely where there are elements that
are longer than a single character involved.
This affects all of these operators presently:
:= != && << <= <=> <> >= >> ||
And shouldn't take too long

-- 
Robin Hugh Johnson
E-Mail     : robbat2 at orbis-terrarum.net
Home Page  : http://www.orbis-terrarum.net/?l=people.robbat2
ICQ#       : 30269588 or 41961639