nextcloud-gmbh / mtime_fixer_tool_kit

Tool kit to fix the mtime issue on the server state
15 stars 7 forks source link

ensure we properly escape such that special characters will work #6

Closed mgallien closed 2 years ago

mgallien commented 2 years ago

Signed-off-by: Matthieu Gallien matthieu.gallien@nextcloud.com

X-dark commented 2 years ago

Latest patch version is not working with pgsql. Filenames are interpreted as column name instead of value.

X-dark commented 2 years ago

For postgresql, sed escaping is unnecessary. What works for me is:

mtime_in_db=$(
  psql \
    "postgresql://$db_user:$db_pwd@$db_host/$db_table" \
    --tuples-only \                                                                                                                                                     
    --no-align \
    -E 'UTF-8' \
    --command="\
      SELECT mtime
      FROM oc_storages JOIN oc_filecache ON oc_storages.numeric_id = oc_filecache.storage \
      WHERE oc_storages.id='home::$username' AND oc_filecache.path=\$\$${relative_filepath_without_username}\$\$"
)

This relies on Dollar-Quoted Strings constants: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

mgallien commented 2 years ago

For postgresql, sed escaping is unnecessary. What works for me is:

mtime_in_db=$(
  psql \
    "postgresql://$db_user:$db_pwd@$db_host/$db_table" \
    --tuples-only \                                                                                                                                                     
    --no-align \
    -E 'UTF-8' \
    --command="\
      SELECT mtime
      FROM oc_storages JOIN oc_filecache ON oc_storages.numeric_id = oc_filecache.storage \
      WHERE oc_storages.id='home::$username' AND oc_filecache.path=\$\$${relative_filepath_without_username}\$\$"
)

This relies on Dollar-Quoted Strings constants: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

thanks @X-dark (by the way one of my former colleague is sharing your full name, how funny !)

artonge commented 2 years ago

Adapted the solution for pgsql, and fixed a problem with trailing line return added in the encoded value.

Tested with filename containing ', ", (, and ;.