Validate mysql in php?

Question

I have a Symfony 4 project and I want to store mysql queries in as a string in a mysql database. However, before storing the strings I want to make sure they are valid mysql syntax. Is there a way of doing this? Thanks!

Answer 1

I didn't test it but it should work.

Use the database API you already use in your project to prepare the SQL statements you want to validate then discard them; do not execute the prepared statements.

For example, using PDO, use PDO::prepare() to ask the server to prepare the statement. It returns a PDOStatement object on success (i.e. when the query is correct). Do not call execute() on the returned statement, just discard it (using unset()).

PDO::prepare() returns FALSE or throws an exception on error, depending on how the PDO's error handling is configured.

Answer 2

The easiest way would be to run a query in a new transaction and then roll it back. SQL can get complex to validate especially if you plan to allow MySQL-specific functions. What if a new function gets introduced in next MySQL release? Writing and maintaining a separate SQL validation library seems counterproductive.

Why not to try following:

1. Create a new user for running these queries in your database. This will allow you to manage security e.g. allowing only to use SELECT statement so no one will run DROP DATABASE.

2. Run the user provided statement using the new user created in point 1. Start a new transaction using START TRANSACTION, execute the user provided statement, and rollback with ROLLBACK. Ensure SET autocommit=0 is set as per 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax.

3. If the user provided statement executes without errors it's valid. You don't have to read all the returned rows in your PHP code.

Make sure to check on performance because some statements will be expensive to execute. This functionality can DOS your application.

I'd probably create procedure or function in the database. That's what they are for. Storing SQL in a table just to query it and then execute only results in a redundant round trip between the database and the application.