How to Save an SQL query into a table?

Question

I am saving an SQL query in a database table, so saving the criteria to be used no matter if more records are inputted into the db.

Is there a correct datatype and syntax to use to store the Query statement. I have set the datatype as VARCHAR(1055) as I think that will be enough. Is there a MySQL function that will make sure the text is saved correctly in terms of, quotations and keeping it a single string.

Update: Reason for saving query

We allow the users of the system to create a list of contact details based on other users of the system, so they create the query using a form to select say all users with job type of executive.

The above query is then saved in the database, so that even if a new user is added in the executive job type, his contact details will be included when sending communications.

I think this is the best way to do it...do you have any ideas?

Answer 1

VARCHAR(1055) will never be enough. Just use TEXT, MySQL's data type for arbitrary-length text (also called CLOB in other databases).

More background info:

• Which DATATYPE is better to use TEXT or VARCHAR?

Nonetheless, I think you should probably model your query in one way or another on the application layer, instead of storing plain text SQL in your database. When you change your schema, all of those SQL statements might be wrong. Good luck migrating, then!

Another drawback of your approach is that you're creating a big security issue, if users are allowed to enter arbitrary SQL. A nifty intern whose contract wasn't prolonged might store

DROP DATABASE my_database.

Answer 2

There is no correct data type to store a query.

But you can always strip HTML chars by using HTMLencode chars.

Or you can use the PHP htmlentities() to convert the characters