Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 6

Displaying posts with tag: SQL/PSM (reset)

Capture MySQL Foreign Keys
+0 Vote Up -0Vote Down

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
  [Read more...]
SQL Injection Risks
+1 Vote Up -0Vote Down

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL
  [Read more...]
MySQL Database Triggers
+1 Vote Up -0Vote Down

One of the students wanted an equivalent example to an Oracle DML trigger sample that replaces a white space in a last name with a dash for an INSERT statement. Apparently, the MySQL trigger example in the Oracle Database 11g and MySQL 5.6 Developer Handbook was a bit long. I have to agree with that because the MySQL DML trigger demonstrated cursors and loops in the trigger code.

Triggers can be statement- or row-level actions. Although some databases let you define statement-level triggers, MySQL doesn’t support them. MySQL only supports row-level triggers. Row-level triggers support critical or non-critical behaviors. Critical behavior means the trigger observes an insert,

  [Read more...]
Placement over substance
+0 Vote Up -0Vote Down

I was stunned when a SQL query raised an ERROR 1630 (42000) telling me the SUM function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM function must be on the same line as the SUM keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT function and it seems to me that this parsing inconsistency is problematic.

Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE settings. This

  [Read more...]
Implicit Commit Functions?
+1 Vote Up -0Vote Down

Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL

  [Read more...]
Value or Reference?
+1 Vote Up -0Vote Down

In class today, we reviewed pass-by-value (IN-only mode) parameters and pass-by-reference (INOUT and OUT mode) parameters for stored procedures. The analogy that finally seemed to hit home for the students was linking the modes to the story of Alice in Wonderland.

Here’s the analogy and below is the code to support it:

“A pass-by-value parameter in a procedure is like sending an immutable copy of Alice into the rabbit hole, which means she

  [Read more...]
Showing entries 1 to 6

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.