0

I need to build a monitor to list all the fields, tables and database every time they are queried.

I would then like to build a script that starting from a query like this:

select
   table_a.field_1, table_a.field_2,
   table_b.field_3
from
   database_I.table_a
   database_II.table_b
where
   table_a.idx = table_b.idx

would get me this array

$objects = array(
   'database_I.table_a.field_1',
   'database_I.table_a.field_2',
   'database_II.table_b.field_3'
)

Playing with ''mysql_field_table'' and ''mysql_field_name'' I can get the table and field name but not the database_name also.

Is there any way to get it just starting from the parsing of the SQL query (altohough it would be a SELECT, INSERT INTO, UPDATE or whatelse)?

Stefano Radaelli
  • 1,088
  • 2
  • 15
  • 35
  • 2
    "Is there any way".. yes, you could of course write a parser, it would certainly be possible. However it's probably too complex a topic for anyone to provide 100% of the code in a quick answer on here. But...wouldn't you just be better to set up logging on the database server rather than trying to parse your SQL strings? – ADyson Jan 18 '19 at 09:12
  • @ADyson is right, actually, your DBs are queried with your PHP application. Nothing stops you from developping another application (mobile, desktop and so on) that queries the same DBs – Cid Jan 18 '19 at 09:16
  • LIke @ADyson said, you could write an intermediary proxy layer between the client and the server or you could utilize existing libraries. Depending on your framework of choice Doctrine ORM already has logging built in and MySQL also has a feature to enable logging. https://stackoverflow.com/questions/12880976/mysql-how-to-track-every-query – Joseph Persico Jan 18 '19 at 09:31
  • @ADyson yes it would! but Database is not under my administration. I need to monitor web pages (on my host) accessing to the remote database to migrate them to another tools under my control. For this reason I cannot enable logging on remote db server but I can only add a monitor on my application. – Stefano Radaelli Jan 18 '19 at 09:43
  • @JosephPersie I already have a log capable to grab any SQL queries (all MySQL connection are allowed by an OO object locally developed offering properties and methods to connect to database). What I don't have is a parser able to extract field, table and database names from the logged SQL query. – Stefano Radaelli Jan 18 '19 at 09:51
  • @StefanoRadaelli You could always ask the maintainers of the database server if there is any way they can help you, before you spend too much time on other solutions. – ADyson Jan 18 '19 at 13:29
  • @StefanoRadaelli But here's another thought...how does your PHP generate these queries? Are they created completely dynamically? Or do you call a specific PHP function which then runs a pre-defined SQL query (but perhaps with parameter variables of course)? And another function to run another query? If so, then a) you already know from your code exactly what tables/fields are called by each query, and can make a list. So then you just need to log every time one of these functions is called, and you therefore know implicitly which tables and fields are being used by the app, and how often. – ADyson Jan 18 '19 at 13:30
  • @ADyson this is the point. I have thousands of script accessing to several differnt database on many different server. All those script pass through a class.database in charge to instantiate connection, run query and provide recordset as output. One of the host is containing a database that has to be dismissed (because not anymore filled) but before switching it off I need a clear screenshot of which projects are accessing it and then to prepare a plan to migrate each script to maintain functionalities. – Stefano Radaelli Jan 19 '19 at 16:10
  • That doesn't really answer my question about how the scripts are generated – ADyson Jan 19 '19 at 17:18

0 Answers0