PHP: using REGEX to get the tablename from a mysql query

Question

Hi Guys, 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,

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

not sure what your trying to accomplish but would this be more useful ? mysql_tablename

Answer 5

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...