PHP Classes

Quick PHP MySQL Parser: Parse MySQL queries into tokens

Recommend this page to a friend!
  Info   View files Example   Screenshots Screenshots   View files View files (2)   DownloadInstall with Composer Download .zip   Reputation   Support forum (1)   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 380 All time: 6,720 This week: 104Up
Version License PHP version Categories
mysql-lexer 1.0GNU Lesser Genera...5.0PHP 5, Databases, Parsers
Description 

Author

This class can parse MySQL queries into tokens.

It can take a given SQL query and parses it to break it down into an array of tokens that can be symbols, functions, comments, etc..

The class can also reformat the query separating the tokens by a single space.

It can also reformat the query replacing the tokens by common strings, so two queries can be compared to determine if they are similar even if they differ in literal values.

Innovation Award
PHP Programming Innovation award nominee
April 2015
Number 6


Prize: SourceGuarding PHP encoder tool
Parsing SQL queries can be useful for applications that need to identify the types of queries and the parameters that they take.

This class can parse SQL queries, so it can be used to reformat different queries to determine if they are similar, even if they differ in literal values.

Manuel Lemos
Picture of Abius X
  Performance   Level  
Name: Abius X is available for providing paid consulting. Contact Abius X .
Classes: 4 packages by
Country: Iran Iran
Age: 36
All time rank: 114810 in Iran Iran
Week rank: 312 Up8 in Iran Iran Up
Innovation award
Innovation award
Nominee: 3x

Winner: 1x

Example

//change $index and run
require_once "QuickMySQLLexer.php";
$queries=<<<XXX
SELECT FOUND_ROWS();
    select "\"",'\'',"'",'"',5;
SELECT * FROM data WHERE ID=-1 UNION ALL SELECT 1,2,group_concat(select * from users where id=1),user() -- ;
SELECT * FROM wp_zotpress_images WHERE citation_id='-1' AND 1=IF(2>1,BENCHMARK(5000000,MD5(CHAR(115,113,108,109,97,112))),0) #';
SELECT COUNT(*) AS Result
FROM
    rbac_userroles AS TUrel
 
    JOIN rbac_roles AS TRdirect ON (TRdirect.ID=TUrel.RoleID)
    JOIN rbac_roles AS TR ON ( TR.Lft BETWEEN TRdirect.Lft AND TRdirect.Rght)
    /* we join direct roles with indirect roles to have all descendants of direct roles */
    JOIN
    ( rbac_permissions AS TPdirect
    JOIN rbac_permissions AS TP ON ( TPdirect.Lft BETWEEN TP.Lft AND TP.Rght)
    /* direct and indirect permissions */
    JOIN rbac_rolepermissions AS TRel ON (TP.ID=TRel.PermissionID)
    /* joined with role/permissions on roles that are in relation with these permissions*/
    ) ON ( TR.ID = TRel.RoleID)
    WHERE
    /* TU.ID=? */
    TUrel.UserID=?
    AND
    TPdirect.ID=?;
select/**/1--d
,2#comment
,'abc'//comment
/**/,4/* hello */,5/* there*/;
select /* good comment*/ hasan/**/, 1-- hello world
,1# good comment
limit 1//last comment;
SELECT `t.name`,hasan/*,1,"#ab>! , 'aa'aasd'''c",3,'123""<>!082ujf=*'*/ FROM users where 1>>1> and "5*/">5 and 1!=5 -- or 1=1
or #hello there
5>4;
SELECT option_name, option_value FROM wp38_options WHERE autoload = 'yes';
SELECT autoload FROM wp38_options WHERE option_name = '_transient_timeout_doing_cron';
SELECT option_value FROM wp38_options WHERE option_name = '_transient_doing_cron' LIMIT 1;
INSERT INTO `wp38_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1427297849.1591129302978515625000', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`);
SELECT t.*, tt.* FROM wp38_terms AS t INNER JOIN wp38_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'post_tag' AND t.name = 'featured' LIMIT 1;
SELECT SQL_CALC_FOUND_ROWS wp38_posts.ID FROM wp38_posts WHERE 1=1 AND wp38_posts.post_type = 'post' AND (wp38_posts.post_status = 'publish') ORDER BY wp38_posts.post_date DESC LIMIT 0, 10;
SELECT wp38_posts.* FROM wp38_posts WHERE ID IN (978,963,933,920,859,838,801,795,790,757);
SELECT t.*, tt.*, tr.object_id FROM wp38_terms AS t INNER JOIN wp38_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp38_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (757, 790, 795, 801, 838, 859, 920, 933, 963, 978*3) ORDER BY t.name ASC;
SELECT post_id, meta_key, meta_value FROM wp38_postmeta WHERE post_id IN (757,790,795,801,838,859,920,933,963,978) ORDER BY meta_id ASC;
SELECT option_value FROM wp38_options WHERE option_name = 'theme_mods_twentyfourteen' LIMIT 1;
SELECT option_value FROM wp38_options WHERE option_name = 'current_theme' LIMIT 1;
SELECT * FROM wp38_posts WHERE (post_type = 'page' AND post_status = 'publish') ORDER BY menu_order,wp38_posts.post_title ASC;
SELECT t.*, tt.* FROM wp38_terms AS t INNER JOIN wp38_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'post_tag' AND t.name = 'featured' LIMIT 1;
SELECT * FROM wp38_users WHERE ID = '2';
SELECT wp38_posts.ID FROM wp38_posts WHERE 1=1 AND wp38_posts.post_type = 'post' AND (wp38_posts.post_status = 'publish') ORDER BY wp38_posts.post_date DESC LIMIT 0, 5;
SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp38_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC ;
XXX;
    $queries=explode(";",$queries);
    $index=0; //change this to try different queries
    $query=$queries[$index];
    $q=new QuickMySQLLexer();
    $tokens=$q->lex($query);
    $mark=$query;//str_repeat(" ",strlen($query));
    echo "Query:",PHP_EOL,PHP_EOL;
    echo $q->query(),PHP_EOL;
    echo str_repeat("-",strlen($query)),PHP_EOL;
    echo "Markings:",PHP_EOL,PHP_EOL;
    echo $q->markings(),PHP_EOL;
    echo str_repeat("-",strlen($query)),PHP_EOL;
    echo "Clean Query:",PHP_EOL,PHP_EOL;
    echo $q->clean(),PHP_EOL;
    echo str_repeat("-",strlen($query)),PHP_EOL;
    echo "Clean Markings:",PHP_EOL,PHP_EOL;
    echo $q->cleanMarkings(),PHP_EOL;
    echo str_repeat("-",strlen($query)),PHP_EOL;
    echo "Structure:",PHP_EOL,PHP_EOL;
    echo $q->structure(),PHP_EOL;


Screenshots  
  • screenshot.png
  Files folder image Files  
File Role Description
Plain text file QuickMySQLLexer.php Class The lexer class
Accessible without login Plain text file example.php Example Example usage code

 Version Control Unique User Downloads Download Rankings  
 0%
Total:380
This week:0
All time:6,720
This week:104Up