Piwigo.org

SourceCoder · 2021-05-01 04:35:18

I have some photos with special chars and I want upload them with Upload Photos [FTP + Synchronization].

There is a parameter $conf['sync_chars_regex'] to set permitted characters for files/directoris during synchronization. To use this parameter add it to the local configuration file config.inc.php under <Piwigo Directory>\local\config\.

Code:

// permitted characters for files/directoris during synchronization
$conf['sync_chars_regex'] = '/^[ %()\'Ɂ¦,;\[\]äöüÄÖÜßa-zA-Z0-9-+=_.]+$/';

If you want to use an apostrophe (') you have to escape this char in $conf['sync_chars_regex'] like this:
\'. I try some other special chars like Ɂ and ¦ if these chars works fine.

There are some problems in SQL-Statments if you want to use an apostrophe (') as a special char, because an apostrophe limit the begin and end of a string.

The solution is to change this file: <Piwigo Directory>\include\dblayer\functions_mysqli.inc.php

Attention: Create a backup file from <Piwigo Directory>\include\dblayer\functions_mysqli.inc.php before you change it!

Line 316:

Old:

Code:

$query.= $separator.protect_column_name($key).' = \''.$data[$key].'\'';

New:

Code:

$query.= $separator.protect_column_name($key).' = \''.addslashes($data[$key]).'\'';

Line 343:

Old:

Code:

$query.= protect_column_name($key).' = \''.$data[$key].'\'';

New:

Code:

$query.= protect_column_name($key).' = \''.addslashes($data[$key]).'\'';

Line 561:

Old:

Code:

$query .= "'".$insert[$dbfield]."'";

New:

Code:

$query .= "'".addslashes($insert[$dbfield])."'";

Line 613:
Old:

Code:

$query .= "'".$value."'";

New:

Code:

$query .= "'".addslashes($value)."'";

The php function addslashes escapes some chars to use them in SQL statements (for further information please read this page: https://www.php.net/manual/de/function.addslashes.php.)

After these changes, i try in Piwigo if special chars in filenames now working. I create a directory E:\testimages and put two images with special chars in it:

[]%()'Ɂ¦,;äöüÄÖÜßa-zA-Z0-9-+=_. - 01.jpg
[]%()'Ɂ¦,;äöüÄÖÜßa-zA-Z0-9-+=_. - 02.jpg
[]%()'Ɂ¦,;äöüÄÖÜßa-zA-Z0-9-+=_. - 03.ico
[]%()'Ɂ¦,;äöüÄÖÜßa-zA-Z0-9-+=_. - 04.webp

Then i create under <Piwigo Directory>\galleries a symbolic link on the directory E:\testimages with the following command:

Code:

mklink /J testimages E:\testimages

After synchronize in Upload Photos [FTP + Synchronization] all images are uploaded with special chars without problems. For the jpg images thumbnails where autogenerate but for the ico images and webp images thumbnails where not autogenerate.

As i check the table images all uploaded images after synchronization have an entry in this table but no md5sum was create for every entry.

Can someone please help me or have an idea to solve these problems?

nicolas · 2021-05-01 07:51:59

SourceCoder wrote:

Code:

// permitted characters for files/directoris during synchronization
$conf['sync_chars_regex'] = '/^[ %()\'Ɂ¦,;\[\]äöüÄÖÜßa-zA-Z0-9-+=_.]+$/';

Your regex looks strange. Do you really want to allow [ and ] in filename ? If yes it's ok. To allow the underscore it must be the last character in the list.

SourceCoder wrote:

Old:

Code:

$query.= $separator.protect_column_name($key).' = \''.$data[$key].'\'';

New:

Code:

$query.= $separator.protect_column_name($key).' = \''.addslashes($data[$key]).'\'';

Line 343:

You'd better use mysql_real_escape_string() instead of addslashes.

SourceCoder · 2021-05-01 10:41:35

To structure files with filenames in a readable way i. e. eBooks, music, videos or photos it is very helpful to use different chars in the filenames.

Examples:

I think this are bad filename styles:

image-1_this-is-a-very-bad-style-isnt-it-id_123456-1989_author-lastname-fistname_[1024x1024].jpg
video-2_documentation_this-is-a-cool-documentation-movie_1994_02-30-59.mp4

I think this are better and readable filename styles:

Image 1 - This Is A Very Bady Style - Isn't ItɁ (ID¦ 123456) [1989]; Author¦ Lastname, Firstname [1024x1024].jpg
Video 2 - Documentation - This Is A Cool Documentation Movie [1994] (02'30'59).mp4

nicolas wrote:
To allow the underscore it must be the last character in the list.

The regex delimiter are at the beginning /^[ and at the end ]+$/?

Okay I change the $conf['sync_chars_regex'] parameter:

Code:

// permitted characters for files/directoris during synchronization
$conf['sync_chars_regex'] = '/^[ %()\'Ɂ!¦,;\[\]äöüÄÖÜßa-zA-Z0-9-+=_.]+$/';

nicolas wrote:
You'd better use mysql_real_escape_string() instead of addslashes.

I think to use mysql_real_escape_string() is not a very good idea, because this was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0 (read the php manual: https://www.php.net/manual/de/function. … string.php).

I think to use mysqli_real_escape_string() works only in php 5 and php 7 (manual: https://www.php.net/manual/en/mysqli.re … string.php). I use php 7.3.

addslashes() works in php 5, php 7 and php 8. So I think this is actual the best way to escape SQL statements.

@nicolas
Do you have some tested php code snippets for Piwigo 11.4.0 to escape SQL statements? I'm very interested in, because may be exist better ways to escape SQL statements with tested code for Piwigo 11.4.0.

Last edited by SourceCoder (2021-05-01 11:04:13)

erAck · 2021-05-01 12:01:01

I doubt this approach hitting everything with a big club to use apostrophes in one specific field content will not break something. As you could see.
If things with (dis)allowed characters were that easy to solve it probably would already had been done. See for example
[Github] Piwigo issue #250
[Github] Piwigo issue #218
[Github] Piwigo issue #201
[Github] Piwigo issue #323

You'd also have to handle all other query places. And you'd have to use stripslashes() whenever content is accessed and should result in the original string.

Also, using addslashes() unconditionally will invalidate all strings that were already treated with addslashes() or similar which then will break things. Or open gates to SQL injection.

As is, I'd never ever add apostrophe to sync_chars_regex. Period.

SourceCoder · 2021-05-01 16:30:07

@erAck:

Stop please! It is a little bit too easy! This was only a consideration to talk to you how to solve problems with special chars to send them over SQL statements to a Piwigo database.

There are four main SQL commands that handles data transactions to a database: 'select', 'insert', 'update' and 'delete'.

'select' reads data from a database. 'insert', 'update' and 'delete' write data to a database. And all these commands are similar among themselves.

For example if you want to send descriptions in different languages from a file or a textarea field to
a database you have to handle these strings with special chars. Such special chars in translations
are very bad to handle.

Demo-Descriptions:

English: This is a text with words which have no special chars.
German: Dies ist ein Text mit Wörtern, die keine Sonderzeichen enthalten.
French: Ceci est un texte avec des mots qui n'ont pas de caractères spéciaux.
Russian: Это текст со словами без специальных символов.
Japanese: これは、特別な文字を含まない単語を含むテキストです。
Hebrew: זהו טקסט עם מילים שאין בהן תווים מיוחדים.
Arabic: هذا نص يحتوي على كلمات ليس لها أحرف خاصة.

All the translations for all languages above work in the description field in Piwigo without errors. There are very bad special chars in the translations and they where send via SQL statement.

But reading filenames with Upload Photos [FTP + Synchronization] cause SQL errors. I do not understand this cause.

Please help me to understand it. Ever main goal for me is to prevent SQL injection and not to cause security risks.

Do I think right or not that this old source code in <Piwigo Directory>\include\dblayer\functions_mysqli.inc.php

Code:

$query.= $separator.protect_column_name($key).' = \''.$data[$key].'\'';

is susceptible for SQL injection?

petitssuisses · 2021-05-01 23:00:53

Easiest solution (if you're not allergic to modify the file names when used by Piwigo) is to install and run the Presynch Autornename plugin.
It automatically transform filenames to conform to Piwigo required naming rules.

Piwigo.org

Announcement

#1 2021-05-01 04:35:18

Upload Photos [FTP + Synchronization] - use files with special chars

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code:

#2 2021-05-01 07:51:59

Re: Upload Photos [FTP + Synchronization] - use files with special chars

SourceCoder wrote:

Code:

SourceCoder wrote:

Code:

Code:

#3 2021-05-01 10:41:35

Re: Upload Photos [FTP + Synchronization] - use files with special chars

nicolas wrote:

Code:

nicolas wrote:

#4 2021-05-01 12:01:01

Re: Upload Photos [FTP + Synchronization] - use files with special chars

#5 2021-05-01 16:30:07

Re: Upload Photos [FTP + Synchronization] - use files with special chars

Code:

#6 2021-05-01 23:00:53

Re: Upload Photos [FTP + Synchronization] - use files with special chars

Board footer