7

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?

Improve this question
9
  • Where are you saving it from? Directly from the console? From PHP? From PhpMyAdmin? Commented Jun 26, 2013 at 10:08
  • 1
    A SQL query by itself is just a string, so varchar seems like the correct data type to me. It's very unusual to save a query, though. I imagine there's a better way to accomplish whatever it is you're trying to accomplish. It also seems like you're creating additional potential SQL injection points in your application, so you'll want to make sure statements are properly sanitized. Commented Jun 26, 2013 at 10:12
  • From PHP, check updated reason for query Commented Jun 26, 2013 at 10:13
  • I suppose your users are not building the query themselves, but you generate that query based on the options they chose from your form? Then it's enough to save those options - if you can build the query from those once, you can do it again. Commented Jun 26, 2013 at 10:23
  • 1
    Like Tuncay Göncüoğlu answered below, storing the individual parameters is favourable. Then when the email needs to be sent - use those parameters and form the query. Commented May 21, 2024 at 7:37

4 Answers 4

Reset to default
4

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Well I think I've protected against that by not allowing direct SQl entry and I never post the query, it's stored in session variables and is generated by the system.
@StephenH: Just wanted to be sure.
1

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

Improve this answer

Comments

1

I believe this is not the right approach. You should not store the resulting query in your database, but instead you should store the parameters that resulted in that created sql. In your implementation when you want to change that query, you'd have to parse your stored SQL to get parameters in order to display your filter form to user. However if you keep your parameters instead, you always can regenerate the query when needed, and easily can display current filter form.

If you do insist of storing your SQL tho, as noted before me, TEXT is the correct field type, as it is much less likely to cut off your sql string at field size limit.

Improve this answer

Comments

0

If you don't need to pass in parameters you could execute a CREATE VIEW. You could just execute the code from PHP using MySQLi.

CREATE VIEW myquery AS
SELECT * FROM mytable

Usage:

SELECT * FROM myquery

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.