Tony Andrews :
Validation can mean a number of things, and can be done in the database in various ways:\n\n\nColumn datatypes are themselves a form of validation: NUMBER columns accept only valid numbers, etc.\nPrimary key, unique and foreign key constraints perform validation\nCheck constraints perform other simple one-row validations such as:\n\nEND_DATE > START_DATE\nSALARY > 0\nJOB = SALESMAN OR COMMISSION IS NULL\n\n\n\nHowever, there are more complex validation rules that cannot be enforced by any of the above, such as:\n - SALARY <= (SELECT max_sal FROM config_table)\n - emp.start_date BETWEEN start_date AND end_date of the department they are assigned to\n\nThere are various ways to enforce these rules, including database triggers, but often the preferred method is to create a stored procedure, often known as an \"API\" to perform the validation and action e.g.\n\nPROCEDURE insert_emp (...) IS\n ...\nBEGIN\n -- Validate\n -- 1) Salary less than max\n SELECT max_sal\n INTO l_max_sal\n FROM config;\n IF p_sal > l_max_sal THEN\n error_pkg.raise_error ('Salary is too high');\n END IF;\n ...\n -- Insert\n INSERT INTO emp (...) VALUES (...);\nEND; \n\n\nThe application can then just call this procedure instead of performing the update directly and all the necessary validation will be performed. In fact, the application would probably have to call this procedure - direct insert into the table would probably be disabled.",
2011-04-18T15:01:56
OMG Ponies :
Data validation occurs because to pass data into a stored procedure, it's done via parameters which are explicitly set to Oracle data types (or user defined types, which are also based on Oracle data types). Only validation of the data type occurs - more in-depth validation has to be constructed if necessary (IE: checking for decimals in a NUMBER data type). Parameterized queries are generally more safe from SQL injection, but it really depends on what the parameters are and what the query is doing.\n\n CREATE OR REPLACE PROCEDURE example (IN_VALUE NUMBER) IS\n\n BEGIN\n\n SELECT t.*\n FROM TABLE t\n WHERE t.column = IN_VALUE;\n\n END;\n\n\nIn this example, submitting a VARCHAR/string will result in an error - anything other than what NUMBER supports will result in an error. And you'll get an error if the IN_VALUE data type can't be implicitly converted to the data type of TABLE.column.\n\nA stored procedure encapsulates a transaction, which is what allows complex processing instructions (meaning, more than one SQL query). Transaction handling (IE: having to explicitly state \"COMMIT\" or \"ROLLBACK\") depends on settings.",
2011-04-18T06:03:12