0

My pre-production task is to create tools to parse MySQL database schema and generate scripts to recreate it completely or partially. I've implemented first part of the task by fetching metadata from INFORMATION_SCHEMA tables and storing it in form of tree-like structure in memory. Now I'm busy generating scripts for each database objects. The issue I encountered is escaping Special Character Escape Sequences (as noted at this page, table 9.1) such as ' and " during writing string literals. Literals can be met in trigger and routine body or in SELECT part of view definition.

When I generate script for creating, say, trigger, literals with unescaped quotes corrupt the query and I cannot run it without manual correction. I get something like this.

Query with syntax error

I would like to be able to run my queries immediately after generating, so I need to write them with escaped quotes. I deviced two possible solution.

  1. Fetch metadata of triggers, routines and views in the way that MySQL escape special sequences in literals. I've been browsing SO and docs for a while and yet to find an appropriate function on the MySQL side. (QUOTE() is not the case as it'll escape every escapable character in routine/trigger definition and even the ones that should not be escaped)

  2. Escape that sequences manually just before script generating. I've tried to figure out the regexp to find and replace such units and here is what I got.

    Pattern pattern = Pattern.compile("\'(.+)\'");
Matcher matcher = pattern.matcher("IF (NEW.AccountBalance <> 0 OR NEW.BlockedAmount <> 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='The account can't be closed!' END IF;");
StringBuilder sb = new StringBuilder();
while (matcher.find()) {
    sb.append("'").append(matcher.group(1).replace("'", "\\\'")).append("'");
}
System.out.println(sb.toString());`

Certainly, this will not work if you've got several literals as I have in my example. It'll escape all single quotes between first and last single quote character and turn two or more literals into one senseless string. I'm guessing is it's possible at all to do it on the app side as regex cannot know where the literal starts and ends. Only me and MySQL can.

I've tried Walid's regexp solution from here and it escaped all target characters in my string, even starting and ending quotes for literals.

I'd highly appreciate any advice how to do it on the app or MySQL side.

Forman
  • 1
  • 2

0 Answers0