Page 6 of 13

Posted: Thu May 31, 2007 8:52 pm
by superdezign
Well, I ran your code and actually got different HTML output than you. Strange. Not much different... It's just as messy.

I think what you need to do is clean up the generated HTML.. Then you probably won't have this many problems.

Posted: Thu May 31, 2007 9:02 pm
by superdezign
I'm messing around with the code, and am finding a lot of little errors. Because I need some practice in regex, I'll see if I can't fix this for you.

Posted: Thu May 31, 2007 9:16 pm
by ziggy3000
Thank YOU :bow: :bow:

Posted: Thu May 31, 2007 10:39 pm
by superdezign
Better formatting. Better regex. Better CSS/HTML. You've just been upgraded! :lol:

Code: Select all

/**
*    Highlight SQL queries
*    @param        string
*/
function HighlightSql($str)
{
    // Create the CSS
    echo '<style type="text/css">.operator{color:green;}.quoted{color:red;}.function{color:blue;}.keyword{color:green;}.condition{color:orange;} .type{color:darkred;}.row{height:1.3em;}.number{clear:left;float:left;width:1.3em;height:1.7em;font-size:.8em;color:#e28000;} .query{margin:1em;padding:1em;background-color:#EEE;border:1px solid #DDD;}</style>';
    
    // Handle quotes
    $str    = preg_replace('/([\'"`])(.+?)([\'"`])/', '<span class="quoted">$1$2$3</span>', $str);
    
    // Handle keywords
    $str    = preg_replace('/(((CREATE|ALTER|DROP)\s+(VIEW|TABLE|DATABASE|SCHEMA))|(SELECT|UPDATE))/i', '<span class="function">$1</span>', $str);
    $str    = preg_replace('/((MAX|AVG|SUM|COUNT|MIN|FROM|INTO)|(ASC|DESC|ORDER BY|LIMIT|LEFT|JOIN|WHERE|MODIFY|CHANGE|DISTINCT))/i', '<span class="keyword">$1</span>', $str);
    $str    = preg_replace('/(LIKE|NOT LIKE|REGEXP)/i', '<span class="condition">$1</span>', $str);
    $str    = preg_replace('/(INT|VARCHAR|TINYINT|BIGINT|BINARY|BIT|BLOB|BOOL|BOOLEAN|CHAR|CHARACTER|DATE|DATETIME|DEC' . '|DECIMAL|DOUBLE|ENUM|FLOAT|FLOAT4|FLOAT8|INT1|INT2|INT3|INT4|INT8|INTEGER|LONGBLOB|' . 'LONGTEXT|MEDIUMBLOB|MEDIUMTEXT|MEDIUMINT|MIDDLEINT|NCHAR|NUMERIC|REAL|SERIAL|SET|SMALLINT|TEXT|TIME|TIMESTAMP|' . 'TINYBLOB|TINYTEXT|VARBINARY|YEAR|PRIMARY|AUTO INCREMENT)/', '<span class="type">$1</span>', $str);
    
    // Handle equal signs, but don't affect equal signs in HTML (the hard part)
    $str    = preg_replace('/(=)(?![\'"][^>]*>)/', '<span class="operator">$1</span>', $str);
    
    // Handle the last of the operators
    $str    = str_replace(';', '<b class="operator">;</b>', $str);
    
    // Create an array to separate by line numbers
    $str    = explode("\n", $str);
    
    // Echo the lines and line numbers
    echo '<b>SQL Query</b>:';
    echo '<div class="query">';
    foreach($str as $id => $data)
    {
        echo '<div class="row number">' . $id . '</div><div class="row">' . $data . '</div>';
    }
    echo '</div>';
}
Enjoy. And be sure to give credit where credit is due. *Ahem* 8)

Posted: Fri Jun 01, 2007 6:32 am
by superdezign
I just noticed some small things by the time I woke up.

1) I separated the results of the regex that gets the quote items in anticipation of giving the quotes a different color. However, since we don't, that pattern can be shrunk to: preg_replace('/([\'"`].+?[\'"`])/', '<span class="quoted">$1</span>', $str);

2) I noticed that, on the regex for the equals sign, it will only work if the equals sign is immediately followed by a single or double quote in an HTML tag. It is possible to omit quotes after an equals sign in and HTML tag, so you could just add \w to the [\'"] character class.

3) You're missing some MySQL keywords. INSERT, for one.

4) The generate HTML markup has no tabbing or newlines. You may want to fix that.

Posted: Fri Jun 01, 2007 9:03 am
by Chris Corbyn
I'm going to nit-pick just because you're learning ;)
superdezign wrote:I just noticed some small things by the time I woke up.

1) I separated the results of the regex that gets the quote items in anticipation of giving the quotes a different color. However, since we don't, that pattern can be shrunk to: preg_replace('/([\'"`].+?[\'"`])/', '<span class="quoted">$1</span>', $str);
That will break on a string like this:

this has a "quoted `string` in it"

Code: Select all

preg_replace('/(([\'"`]).+?\\2/', '<span class="quoted">$1</span>', $str);
;)

Posted: Fri Jun 01, 2007 9:05 am
by feyd
It also doesn't take into account escaped quotes. ;)

Posted: Fri Jun 01, 2007 9:19 am
by superdezign
@d11 Would I need to run the different kind of quotes separately then, so that it doesn't overlap?

@feyd What if I threw in stripslashes()?

Posted: Fri Jun 01, 2007 9:28 am
by feyd
superdezign wrote:@feyd What if I threw in stripslashes()?
Generally, not a good idea.

I've posted regex previously that takes account of escaped quotes when fetching strings. I'll let you poke around for it first. ;)

Posted: Fri Jun 01, 2007 9:42 am
by superdezign
feyd wrote:
superdezign wrote:@feyd What if I threw in stripslashes()?
Generally, not a good idea.

I've posted regex previously that takes account of escaped quotes when fetching strings. I'll let you poke around for it first. ;)
Yessir. (I just thought about how it'd be bad to stripslashes() since the escaped characters are necessary to have single quotes in an inputted value)

So, I'm thinking I'd want to avoid escaped quotes.
I'll take a stab at it before I go searching.

Code: Select all

/(?<!\\)([\'"`])(.+?)(?<!\\)([\'"`])/
It sooo doesn't work. ;_;

Posted: Fri Jun 01, 2007 9:47 am
by feyd
Unfortunately, the backslashes can be escaped too. So you have to be careful to detect that too. ;)

As example of such a pattern can be found in: viewtopic.php?t=65940

Posted: Fri Jun 01, 2007 9:57 am
by superdezign
Whoa... I haven't see ?: before.

Time to read up on more regex. ^_^

Posted: Fri Jun 01, 2007 10:07 am
by superdezign
What's weird is that the regex I posted earlier works for a string like 'Say hi to \'feyd\' later' in Regex Coach, but in PHP I get this error:

Code: Select all

Warning: preg_replace() [function.preg-replace]: Compilation failed: missing ) at offset 27 in c:\apache\htdocs\milg\regex.php on line 12

Posted: Fri Jun 01, 2007 10:13 am
by feyd
you need four backslashes to produce one in regex due to PHP needing escaping and the regex needing escaping as well.

Posted: Fri Jun 01, 2007 10:17 am
by superdezign
Okay, so while I'm search for characters that Google doesn't recognize... :lol:

Here's my solution thus far:

Code: Select all

(?<![\\\\\/])([\'"`])(.+?)(?<![\\\\\/])([\'"`])

And could you tell me what ?: is called?