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?
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:
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With