Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Export Grid Rows - Does not escape values

Wiikend's profile image Wiikend posted 1 month ago in General Permalink

Hi,

I'm trying to export grid rows to a file as SQL inserts, and run the inserts in an identical table in another database. The problem is that one of the fields in the exported rows contains a "'" (single quote) as part of a valid street address.

HeidiSQL exports the rows as SQL inserts without failure, but when running the SQL inserts against the other DB, I get a syntax error because of the unescaped single quote. See image below for my settings.

Description

Is there a way to get around this in today's HeidiSQL? Is it possible to take into account characters that need to be escaped when exporting grid rows to SQL inserts? (I guess this will also affect the other SQL command exports)

Thank you in advance!

ansgar's profile image ansgar posted 1 month ago Permalink

I just did the same, from the data grid of a one-row table, containing a VARCHAR column with one single quote in it:

Description

The result is properly escaped:

INSERT INTO `curly` (`id`, `executed`, `url`, `total_time`, `http_code`, `caller_ip`, `debug`) VALUES (1, '2020-01-21 21:06:17', '\'', NULL, NULL, NULL, NULL);

Probably you are running an older version, or on a different server system?

Wiikend's profile image Wiikend posted 1 month ago Permalink

In the example above I was on MariaDB 5.5.52 using HeidiSQL 10.3.0.5771. The MariaDB version is old, but it's the one we have in production right now. Do you think that it could be the old version of MariaDB causing the issue?

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Wiikend's profile image Wiikend posted 1 month ago Permalink

After taking a closer look, it's not a normal single quote. Have a look at this: ′ It's one of those styled single quotes, I don't even know how to type one from my keyboard. It's not a backtick, and it's not a "reversed backtick" either. How this user got this thing into my database, I have no idea, but it was probably copy/pasted from a website somewhere.

Just out of curiosity, is there a way to deal with that one? Is the escaping handled on your end or by the DB server?

Wiikend's profile image Wiikend posted 1 month ago Permalink

Don't get me wrong, when I said "your end", I meant HeidiSQL. I'm not expecting you to come escape this input for me personally (although that would probably make my (and everyone in the office)'s day! ;)

ansgar's profile image ansgar posted 1 month ago Permalink

Hehe :) Are you sure it is this special quote character which causes an error, or is it probably due to something else?

Wiikend's profile image Wiikend posted 1 month ago Permalink

Well, it was the only line that failed from a set of around 150 lines, and I can't see anything else in particular that would cause problems, but when I get off work today I'll try to test it further (by e.g. removing the single quote or inserting null into the cell I think is the problem, and try again). I'll let you know how it goes.

Please login to leave a reply, or register at first.