search

mrAceT / nextcloud-S3-local-S3-migration

Script for migrating Nextcloud primary storage from S3 to local to S3 storage
GNU General Public License v3.0
67 stars 11 forks source link

SQL escape special characters #5

Closed dragonpil closed 1 year ago

dragonpil commented 1 year ago

when filename contains special caracters like a ' they are not escaped and this leads to an sql error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxxxxxx.docx' AND ST.numeric_id = FC.storage AND FC.mimetype ...' at line 1 in /var/www/nextcloud-S3-local-S3-migration/localtos3.php:393 Stack trace:

0 /var/www/nextcloud-S3-local-S3-migration/localtos3.php(393): mysqli->query('SELECT ST.`id...')

1 {main}

thrown in /var/www/nextcloud-S3-local-S3-migration/localtos3.php on line 393

dragonpil commented 1 year ago

This issue just appears when s3 is not empty. Bevor I used the script I tried to sync all files to s3 with aws s3 sync..., the script saves the data with ids and not with the filename and path. so no issue just my wrong usage ;)

mrAceT commented 1 year ago

Thank you for fixing it yourself ;)

For future reference:

I am assuming the trouble was at this line: https://github.com/mrAceT/nextcloud-S3-local-S3-migration/blob/4ab29ef45beecfb73d51612a375de792eda0ba24/localtos3.php#L398

The objects (files) are read via 'S3list' here: https://github.com/mrAceT/nextcloud-S3-local-S3-migration/blob/4ab29ef45beecfb73d51612a375de792eda0ba24/localtos3.php#L364

That is loaded via an AWS-S3 function, the results are the "internal file names" used by Nextcloud (structure urn:oid:123) the last part is a number. If somehow (by accident) there are already files in the S3 bucket (especially with "odd characters") then the script will fail because the first mentioned line does not escape the content.

I just published version 0.36 with this to nicely catch this:

    if ( !preg_match('/^[0-9]+$/',substr($object['Key'],8)) ) {
      echo "\nFiles in the S3 bucket should be of structure 'urn:oid:[number]',".
           "\nThe bucket that Nextcloud uses may only contain files of this structure.".
           "\nFile '".$object['Key']."' does not conform to that structure!\n";
      die;
    }