MySQL highlighter
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Better formatting. Better regex. Better CSS/HTML. You've just been upgraded! 
Enjoy. And be sure to give credit where credit is due. *Ahem* 
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>';
}- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.
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.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
I'm going to nit-pick just because you're learning 
this has a "quoted `string` in it"

That will break on a string like this: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);
this has a "quoted `string` in it"
Code: Select all
preg_replace('/(([\'"`]).+?\\2/', '<span class="quoted">$1</span>', $str);- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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)feyd wrote:Generally, not a good idea.superdezign wrote:@feyd What if I threw in stripslashes()?
I've posted regex previously that takes account of escaped quotes when fetching strings. I'll let you poke around for it first.
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
/(?<!\\)([\'"`])(.+?)(?<!\\)([\'"`])/- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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
As example of such a pattern can be found in: viewtopic.php?t=65940
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Okay, so while I'm search for characters that Google doesn't recognize... 
Here's my solution thus far:
And could you tell me what ?: is called?
Here's my solution thus far:
Code: Select all
(?<![\\\\\/])([\'"`])(.+?)(?<![\\\\\/])([\'"`])And could you tell me what ?: is called?