News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Syntax errors from SMF database backup.

Started by Antechinus, April 14, 2009, 06:13:29 PM

Previous topic - Next topic

Antechinus

As per this topic: http://www.simplemachines.org/community/index.php?topic=303006.0

The phpinfo.php for the server is located here.

All magic_quotes variables are turned off.

The account I am using is resticted to my domain and does not have root access.
Php is running as a module, whatever that means and if it makes any difference.

Problem: when backing up from SMF admin any apostrophes are being saved as \' instead of the correct version (for SQL) which is ''
This causes a syntax error for each and every apostrophe in the database, with consequent problems with broken links and dropped content if restoring the backup.
This applies to both 1.1.x and SMF 2 RC1 sites. I have tested it on both.

ETA: I can zip up some examples of backups and post them in the team boards if anyone wants to take a look at them for comparison. Full access to phpMyAdmin, etc is available too of course.

SleePy

They shouldn't be saved as \'.
SMF is storing in the file as single quotes.

Is it like this?
Quote
'\'Text\''

Can I see a sample set of the backup?
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

Antechinus

#2
Sure. Would you like a complete from one of the test sites? Very small. Only about 45 kb unzipped.

ETA: Here's an example from the calendar holidays table.

# Dumping data in `smf_calendar_holidays`
#

INSERT INTO `smf_calendar_holidays`
(`ID_HOLIDAY`, `eventDate`, `title`)
VALUES (1, '0004-01-01', 'New Year\'s'),
(2, '0004-12-25', 'Christmas'),
(3, '0004-02-14', 'Valentine\'s Day'),
(4, '0004-03-17', 'St. Patrick\'s Day'),
(5, '0004-04-01', 'April Fools'),
(6, '0004-04-22', 'Earth Day'),
(7, '0004-10-24', 'United Nations Day'),
(8, '0004-10-31', 'Halloween'),
(9, '2004-05-09', 'Mother\'s Day'),
(10, '2005-05-08', 'Mother\'s Day'),
(11, '2006-05-14', 'Mother\'s Day'),
(12, '2007-05-13', 'Mother\'s Day'),
(13, '2008-05-11', 'Mother\'s Day'),
(14, '2009-05-10', 'Mother\'s Day'),
(15, '2010-05-09', 'Mother\'s Day'),
(16, '2011-05-08', 'Mother\'s Day'),
(17, '2012-05-13', 'Mother\'s Day'),
(18, '2013-05-12', 'Mother\'s Day'),
(19, '2014-05-11', 'Mother\'s Day'),
(20, '2015-05-10', 'Mother\'s Day'),

SleePy

I don't see what is wrong there?
That is how they should be saved. When we want to use a single apostrophe, and we have opened the string with a single apostrophe, we have to escape it with a \. The same apples for the double apostrophe.
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

Antechinus

#4
Well I can tell you for a fact that when such a backup is opened in Notepad++ the syntax highlighting shows an error for every  \'  and I can also tell you that when such a backup is restored there is missing content and broken links all over the place. So to my mind this indicates quite a problem.

On the other hand when I get a backup with the apostrophes saved in a different format (either from my local host or by exporting a table directly from phpMyAdmin) then the syntax highlighting is correct and restoring does not result in dropped content.

What this means is that even if you think \' is the correct syntax my server happens to disagree with you, and so does the bloke who programmed Notepad++.

ETA: I'll give you two different examples of syntax that does work.

First, this:

-- Dumping data for table `smf_calendar_holidays`
--

INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(1, '0004-01-01', 'New Year''s');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(2, '0004-12-25', 'Christmas');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(3, '0004-02-14', 'Valentine''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(4, '0004-03-17', 'St. Patrick''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(5, '0004-04-01', 'April Fools');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(6, '0004-04-22', 'Earth Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(7, '0004-10-24', 'United Nations Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(8, '0004-10-31', 'Halloween');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(9, '2004-05-09', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(10, '2005-05-08', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(11, '2006-05-14', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(12, '2007-05-13', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(13, '2008-05-11', 'Mother''s Day');


Note that the apostrophes are saved as ''. This is actually two singe apostrophes, not one double. This works for both syntax highlighting in Notepad++ and for restoration of content without any dropped sections, etc.

The other syntax which works is this:

# Dumping data in `smf_calendar_holidays`
#

INSERT INTO `smf_calendar_holidays`
(`ID_HOLIDAY`, `eventDate`, `title`)
VALUES (1, '0004-01-01', 'New Year's'),
(2, '0004-12-25', 'Christmas'),
(3, '0004-02-14', 'Valentine's Day'),
(4, '0004-03-17', 'St. Patrick's Day'),
(5, '0004-04-01', 'April Fools'),
(6, '0004-04-22', 'Earth Day'),
(7, '0004-10-24', 'United Nations Day'),
(8, '0004-10-31', 'Halloween'),
(9, '2004-05-09', 'Mother's Day'),
(10, '2005-05-08', 'Mother's Day'),
(11, '2006-05-14', 'Mother's Day'),
(12, '2007-05-13', 'Mother's Day'),
(13, '2008-05-11', 'Mother's Day'),
(14, '2009-05-10', 'Mother's Day'),
(15, '2010-05-09', 'Mother's Day'),
(16, '2011-05-08', 'Mother's Day'),
(17, '2012-05-13', 'Mother's Day'),
(18, '2013-05-12', 'Mother's Day'),
(19, '2014-05-11', 'Mother's Day'),
(20, '2015-05-10', 'Mother's Day'),
(21, '2016-05-08', 'Mother's Day'),


Again, this is fine for both highlighting in Notepad++ and for error-free restoration of content. Of course I'm currently running ISO not UTF-8.

Anyway the point is that the current way that both SMF and the cpanel SQL manager want to save apostrophes does not work in practice. It's that simple. The only way I can get an uncorrupted table exported from my online server is to export straight from phpMyAdmin, and when I do that apostrophes are not saved as \'

When I export from my local host using SMF admin the backups are fine, and once again the apostrophes are not saved as \'

This has to be a server configuration problem. Something in the way my online server is set up is causing both SMF admin and the cpanel SQL manager to bugger the syntax in backups. 

SleePy

I'm sorry, but apostrophes in text need to be escaped. Not escaping them with a \ will break anything you try to import it into as then it thinks it left that value and is trying to do another value.

Your syntax of what does work, won't really work.
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

Antechinus

Well I'm happy to believe you but my server apparently wants to argue. I mean I have tested this and the results are as I posted.

ThorstenE

Quote from: SleePy on May 23, 2009, 01:44:43 PM
I'm sorry, but apostrophes in text need to be escaped. Not escaping them with a \ will break anything you try to import it into as then it thinks it left that value and is trying to do another value.
That's right..
this snippet was exported via shell command mysqldumb:
(32,'2018-06-17','Father\'s Day')

Antechinus

#8
Ok. Then why doesn't it work on my server, and why does phpMyAdmin export with a different syntax that does work? Any ideas on that, because it's making it impossible for me to get uncorrupted backups?

I can't use phpMyAdmin because it times out. I can't use SMF admin or the cpanel database backup utility or MySqlDumper either.

ETA: Note I am not talking about what is supposed to happen in theory. I'm talking about what actually happens on my particular server. If there is something wrong with the way my server is set up then that's fair enough, but it would be very handy to be able to find out what it is.

ETA2: And there is a stack of information online stating that yes, apostrophes do ned to be escaped but that using \' is not the way to do it. If you are going to tell me that \' is correct then please explain why the following sites are wrong, and why their suggested syntax works on my sites. I cannot afford to drop this topic as getting a resolution to this is critical for backing up my site and I know it affects some other sites to, because I've had to deal with their databases.

http://www.kamath.com/codelibrary/cl003_apostrophe.asp

http://www.4guysfromrolla.com/webtech/051899-1.shtml

http://www.sqlteam.com/article/apostrophes-and-quotation-marks-in-sql-server

http://www.codefixer.com/tutorials/apostrophe.asp

From these search results:

http://www.google.com.au/search?q=apostrophes+sql&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GB:official&client=firefox-a

ThorstenE

the right way is escaping the ' with the \
php has its own function for this:
http://de.php.net/manual/en/function.mysql-escape-string.php

maybe your php.ini is configured wrong, check values for magic_quotes_runtime and magic_quotes_gpc
http://de3.php.net/manual/en/security.magicquotes.disabling.php

The above links are outdated (check the post date) and besides not (My)SQL specific. I haven't read them in detail but I believe all articles are MSSQL related.

Compuart

After doing some research, I'm pretty sure your problem might have been caused by the NO_BACKSLASH_ESCAPES MySQL mode. There's probably no way to support all MySQL modes possible. 2.0 should do it right, as it uses the official escaping method supplied by MySQL and based on the current database connection. If it doesn't support the specific MySQL mode, it's basically a bug in PHP (or mysqllib, not sure). It's pretty much impossible to meddle with the mysql_real_escape_string function as it takes character set support into account.
Hendrik Jan Visser
Former Lead Developer & Co-founder www.simplemachines.org
Personal Signature:
Realitynet.nl -> ExpeditieRobinson.net / PekingExpress.org / WieIsDeMol.Com

Compuart

Possibly we might be able to change the MySQL mode, but it would probably require specific database permissions, which a user might not have :-\
Hendrik Jan Visser
Former Lead Developer & Co-founder www.simplemachines.org
Personal Signature:
Realitynet.nl -> ExpeditieRobinson.net / PekingExpress.org / WieIsDeMol.Com

Antechinus

#12
Quote from: TE on June 04, 2009, 01:15:09 PMmaybe your php.ini is configured wrong, check values for magic_quotes_runtime and magic_quotes_gpc
http://de3.php.net/manual/en/security.magicquotes.disabling.php
Been there, done that.  ;)  From the OP:

Quote from: Antechinus on April 14, 2009, 06:13:29 PMThe phpinfo.php for the server is located here.

All magic_quotes variables are turned off.


Quote from: Compuart on June 04, 2009, 04:54:54 PM
After doing some research, I'm pretty sure your problem might have been caused by the NO_BACKSLASH_ESCAPES MySQL mode. There's probably no way to support all MySQL modes possible. 2.0 should do it right, as it uses the official escaping method supplied by MySQL and based on the current database connection. If it doesn't support the specific MySQL mode, it's basically a bug in PHP (or mysqllib, not sure). It's pretty much impossible to meddle with the mysql_real_escape_string function as it takes character set support into account.
Ok cool. This looks promising. Note that I have tried this with an RC1 test site and I get the same result, so it's looking like 2.0 has the same problem. I can do some more testing and if necessary ask my host about MySQL modes.

If it isn't possible to mess around with mysql_real_escape_string function would changing character sets help? The native format for the db connection is utf-8 but the forum itself runs latin_swedish for the tables. Is it possible that using utf-8 for everything would make a difference?


Quote from: Compuart on June 04, 2009, 04:58:59 PM
Possibly we might be able to change the MySQL mode, but it would probably require specific database permissions, which a user might not have :-\
I can probably either arrange permissions or get the host to sort it. Their support is very good.

If we can nail this one down it would be great, because from what I've seen this is the only thing stopping the SMF admin backup tool from turning out flawless 20meg gzips on demand. I haven't tried to push it higher than that yet but I will soon. Anyway, if it can be made totally reliable up to 20 meg that would help a lot of people out, and if doing this is known to require a specific MySQL mode then a note could be made in the requirements section of the docs.

If there is anything specific that you would like me to test let me know.

Antechinus

The backup timing out in phpMyAdmin isn't the problem. It's the syntax of backups that are generated by scripts that don't time out. ;)
I'm still doing some more testing.

zavag

#14
Except all the errors from a Database backup there is another one missing but VERY USEFUL function:
The RESTORE option from a previous backup on Admin's Control Panel

(PhpMyAdmin is a tool .... SMF is an integrated forum management environment)

GravuTrad

#15
Which version of phpmyadmin do you use? magic_quotes_gpc is on or off? (seen, yours is off so ok)
On a toujours besoin d'un plus petit que soi! (Petit!Petit!)


Think about Search function before posting.
Pensez à la fonction Recherche avant de poster.

Norv

It's interesting to note that it seems mysqldump uses backslahes for escaping strings, as expected, and it's only phpmyadmin it seems, which doesn't.
Worth checking however the status of that option mentioned above: NO_BACKSLASH_ESCAPES. Asking for variables, I saw that SQL mode was empty, though, for one of the two servers on which this issue is reported... I'll check the second as soon as possible.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Antechinus

Want to merge this with the other thread we started yesterday?

Norv

Well I won't do that by default tbh - it is not public. I have nothing against it, myself, though.
But basically it says the same things after all.

It'd be useful to know anything about your phpmyadmin too, IMHO.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Antechinus

What OS are you running on your home box?

Advertisement: