3

For example, say I'm creating a table which stores promo codes for a shipping site. I want to have the table match a promo code with the code's validator, e.g.

PROMO1: Order must have 3 items
PROMO2: Order subtotal must be greater than $50

I would like to store the query and/or routine in a column in the table, and be able to use the content to validate, in the sense of 

SELECT * FROM Orders 
WHERE Promo.ID = 2 AND Promo.Validation = True

Or something to that effect. Any ideas?

shiser
  • 262
  • 1
  • 3
  • 13
  • 2
    This is a _really_ bad idea. You do not want to store your validation logic as queries within the database. Have validation happen on the application and have constraints (perhaps use `CHECK` constraints if available) to ensure data integrity. – Oded Jun 30 '12 at 22:07
  • And what, then a programmer has to update the application code every time we add a promo, as opposed to one of the less-technical content writers being able to enter something in a content management system? – shiser Jun 30 '12 at 22:53

2 Answers2

3

I wouldn't save the query in the database, there are far better possibilities.

You have to decide, which best fits your needs (it's not clear for me based on your question). You can use

Views

or

Prepared Statements

or

Stored Procedures

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • As clarification: When you go to a shopping site, if you type in a promo code in the appropriate field, it will either tell you it's valid or invalid, and if it's valid, apply it. I want to set the system up in an easily extensible way so that promo code/promo code logic pairs are easily addable... – shiser Jun 30 '12 at 22:57
  • Then put your validator in a Stored Procedure with the promo code as parameter. – fancyPants Jun 30 '12 at 23:04
  • I'm still not clear why it's better for the validator to be in a Stored Procedure than in a Table... – shiser Jul 02 '12 at 01:53
  • First of all there's the overhead. You select your query from DB, then build your statement, then execute this statement. I don't even know how you came to that idea. Then you better google "sql injection". I can't even think of one positive aspect of your approach. – fancyPants Jul 02 '12 at 08:15
  • No need for pedantry. SQL Injection is a problem no matter what, and of course I would be sanitizing any input, whether or not I used this approach. I came up with the idea as part of a concept to create a dynamic code system that would be usable by the non-technically-inclined users without needing my intervention. – shiser Jul 11 '12 at 18:02
  • @shiser Didn't mean to sound harsh or anything. :) – fancyPants Jul 11 '12 at 19:58
1

There's probably a better way to solve the issue, but the answer to your question is to write stored procedures that return the results you want. Where I work (and I hate this design), they actually store all queries and dml used by the application in stored procedures.

You can also dynamically build your queries using dynamic sql. For MySql, see the post below which might be of some help to you.

How To have Dynamic SQL in MySQL Stored Procedure

Otherwise, you can also store your queries in a string format in the database, and retrieve them and execute them using the EXECUTE statement, such as that post points out.

I'd personally keep away from designs like that though. Storing queries in XML isn't a bad alternative, and have your app be written to be extensible and configurable from XML, so you don't need to make code changes to add new validation logic, and instead just have to configure it in XML.

Community
  • 1
  • 1
Jim
  • 6,753
  • 12
  • 44
  • 72
  • Your dynamic SQL is also known as prepared statements. Just for clarification. – fancyPants Jul 01 '12 at 10:11
  • Yes, in MySQL it's prepared statements, but generally the same concept as dynamic SQL in other implementations. – Jim Jul 01 '12 at 15:21