MySQL highlighter

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
ziggy3000
Forum Contributor
Posts: 205
Joined: Fri Mar 23, 2007 3:04 pm

Post by ziggy3000 »

Thank YOU :bow: :bow:
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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);
;)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It also doesn't take into account escaped quotes. ;)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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()?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. ;)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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. ;_;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Whoa... I haven't see ?: before.

Time to read up on more regex. ^_^
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you need four backslashes to produce one in regex due to PHP needing escaping and the regex needing escaping as well.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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?
Post Reply