Consider these three mysql statements:
select * from Users;
select id, title, value from Blogs;
select id, feelURL, feelTitle from Feeds where id = 1;
Now im not very good at REGEX, but i want to get the table name from the mysql query. Could someone possibly create one for me with a little explanation.
Thanks,
You can actually use MySQL as the parser and get the tablenames in your query no matter how complex your SQL syntax.
(Sorry that this is a late response to your question - I just had the same problem today and found this solution.)
Simply prefix your query with the word EXPLAIN and the results set returned to PHP will include id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra. The third column is the name of each table in your query.
For example, if your query was:
select count(*) from ey_def left join ey_rels on def_id=item_id;
Use:
explain select count(*) from ey_def left join ey_rels on def_id=item_id;
And MySQL will return this to PHP:
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | ey_def | index | NULL | PRIMARY | 4 | NULL | 87 | Using index |
| 1 | SIMPLE | ey_rels | ALL | NULL | NULL | NULL | NULL | 123 | |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
Now you can simply process the results like any other query.
AS). For example: SELECT count(*) FROM ey_def AS eyd LEFT JOIN ey_rels AS eyr ON eyr.def_id=eyd.item_id will return 'eyd' and 'eyr' as the tables and not the actual table names.Try:
preg_match('/\bfrom\b\s*(\w+)/i',$query,$matches)
This will not work if the query has more than 1 table.
Basically the regex searchs for the complete word FROM in the query and picks the following word as the table name.
A naive implementation would be this:
preg_match("/\s+from\s+`?([a-z\d_]+)`?/i", $query, $match);
echo $query . " => " . $match[1] . "\n";
This will break when you have a subquery in your SELECT field list (and probably in a few other cases). Or when your table name contains characters beside a-z, numbers and underscores.
Parsing SQL correctly isn't trivial.
For the query string you gave, the following should do:
preg_match_all('/from (\w+)/', $query, $tables);
print_r($tables[1]);
[0] => Users
[1] => Blogs
[2] => Feeds
But like pointed out in a comment already, creating a full fledged SQL parser is a non-trivial task. Don't expect this to be usable on any and all queries you throw against it.
Wish I would have seen this earlier... Like the people above me stated, it's non-trivial parsing sql statements. To pick out the table names from a sql string, it would be a better idea to get all the table names first, then find matches in the sql (providing you don't have a million tables in your database). I just so happen to have a function on hand that does just that:
/*
Takes a sql statement and attempts to get a table name from it.
This assumes a database is already specified in the connection.
[$sql]: string; SQL statement that was executed
[$conn]: resource; MySQLi connection resource
returns table name string
*/
function get_table_names($sql,$conn){
//declare variables
$table_array = array();
$table_string = "";
//get all the table names in the selected database
$sql2 = "SHOW TABLES";
$result = mysqli_query($conn, $sql2);
//display an error if something went wrong
if (!$result) {
echo "DB Error, could not list tables\n";
echo 'MySQL Error: ' . mysqli_error($conn);
exit;
}
//fetch the rows and push the table names into $table_array
while ($row = mysqli_fetch_row($result)) {
array_push($table_array, $row[0]);
}
//loop through all the tables in the database
foreach($table_array as $table){
if(strpos($sql,$table)){ //if match is found append to string
$table_string .= " $table ";
}
}
//return a string of table name matches
return $table_string;
}
Hope that helps someone...
This should do it:
(SELECT|DELETE|UPDATE|INSERT INTO) (\*|[A-Z0-9_]+)(FROM)?([A-Z0-9_, ]+)
It will works with select delete update and insert. If you use tablename1, tablename2 it will return it as a array
While perfect Regex parsing of every possible MySQL query would be extraordinarily complex, something like this will work in a pretty substantial number of normal/basic use-cases:
(?:[\s](?:(?:CREATE|DROP) (?:TEMPORARY )?TABLE|EXISTS|FROM|JOIN|UPDATE|INTO)[\s]*)(?:`(?<fieldName>[a-z][-a-z_\d]*)`([\s]+AS[\s]+`?[a-z\d_-]+`?)?(?:,[\s]*)?)+/i
This returns matches for any words that start with a letter, and occur within back-tick quotes ( ` ) immediately after any instance of the terms EXISTS, FROM, JOIN, UPDATE and INTO as well as most common forms of CREATE TABLE and DROP TABLE. This clearly depends on using the optional INTO keyword on relevant queries (i.e. INSERT, REPLACE) and putting table references within back-ticks, but I don't think those are unreasonable requirements and doing both certainly helps minimise false positives.
So it should relatively-reliably catch table names from typical queries like these:
SELECT FROM `tableName`[, `tableName2`...] [AS...] [LEFT|RIGHT] JOIN `joinTable` [AS...]
INSERT INTO `tableName` [AS...]
DELETE FROM `tableName` [AS...]
UPDATE `tableName` [AS...] SET VALUES...
CREATE TABLE `tableName`[, `tableName2`...]
DROP TEMPORARY TABLE `tableName`
CREATE TABLE IF NOT EXISTS `tableName`[, `tableName2`...]
Obviously, being as this is a simple regex string parser, any time the parsed query actually contains a string which itself mimics any of the relevant formats of a query as data, would falsely be caught as well...
