alembic
alembic copied to clipboard
sqlalchemy
document that URLs need to be URL escaped, and if present in alembic.ini also need percent signs doubled
Describe the bug Alembic cannot consume database URIs that contain special characters.
Expected behavior Expected behavior is either a nice error message, or preferably consuming the database URI string without complaint.
To Reproduce
Create a new, minimal Alembic repository. In the env.py, add a line like config.set_main_option("sqlalchemy.url", "postgres://test:@#$%^&*()@localhost/test"). Then attempt to create a revision and upgrade the database.
Alternatively, I have an example repository.
Error
Traceback (most recent call last):
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/bin/alembic", line 8, in <module>
sys.exit(main())
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/config.py", line 577, in main
CommandLine(prog=prog).main(argv=argv)
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/config.py", line 571, in main
self.run_cmd(cfg, options)
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/config.py", line 548, in run_cmd
fn(
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/command.py", line 298, in upgrade
script.run_env()
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
util.load_python_file(self.dir, "env.py")
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
module = load_module_py(module_id, path)
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 783, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "alembic/env.py", line 12, in <module>
config.set_main_option("sqlalchemy.url", "postgres://test:@#$%^&*()@localhost/test")
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/config.py", line 242, in set_main_option
self.set_section_option(self.config_ini_section, name, value)
File "/home/sky/.cache/pypoetry/virtualenvs/alembic-test-8N3_MNVT-py3.8/lib/python3.8/site-packages/alembic/config.py", line 269, in set_section_option
self.file_config.set(section, name, value)
File "/usr/lib/python3.8/configparser.py", line 1201, in set
super().set(section, option, value)
File "/usr/lib/python3.8/configparser.py", line 894, in set
value = self._interpolation.before_set(self, section, option,
File "/usr/lib/python3.8/configparser.py", line 402, in before_set
raise ValueError("invalid interpolation syntax in %r at "
ValueError: invalid interpolation syntax in 'postgres://test:@#$%^&*()@localhost/test' at position 19
Versions.
- OS: WSL 2 with Ubuntu 20.04
- Python: 3.8.2
- Alembic: 1.4.2
- SQLAlchemy: 1.1.0
- Database: Latest Postgres image on Docker
- DBAPI:
Additional context
This is relevant because, if a DB URI's password is naively escaped
(such as with urllib.parse.quote) it will contain many many % signs. E.g. @#$%^&* will turn into %40%23%24%25%5E%26%2A. I think that ideally the config interpolation should not use % interpolation?
Assuming this is a simple issue to fix, I intend to open a PR to try and address this.
The problem is that the quoted version still contains naked %, but it seems like Alembic expects them to be %% somewhere within its code.
On Fri, May 29, 2020, 16:36 Federico Caselli [email protected] wrote:
Hi, I think you need to quote the url in this case. See the documentation https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=create_engine#database-urls on sqlalchemy
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlalchemy/alembic/issues/700#issuecomment-636008648, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA22VOZBCAR32ZO4UIOZXELRT7B7LANCNFSM4NOBFVKA .
If you quote '@#$%^&*'
>>> urllib.parse.quote_plus('@#$%^&*')
'%40%23%24%25%5E%26%2A'
so there is not naked %
hi, are you using flask-migrate? you would be experiencing https://github.com/miguelgrinberg/Flask-Migrate/issues/328 if so which has an issue in that it rewrites the URL a second time. there are workarounds present there.
If you quote
'@#$%^&*'>>> urllib.parse.quote_plus('@#$%^&*') '%40%23%24%25%5E%26%2A'so there is not naked
%
As zzzeek said, because alembic uses configparser the "%" has to be double escaped into %%s for it to be valid. This isn't obvious either in the documentation or in the error message. I think it would be helpful if they were updated to make this clear.
Also, as the example repository shows, I'm just using Alembic and sqlalchemy to reproduce this error.
I created a SQLite database:
sqlite3 db%file.db
put it in alembic.ini as:
sqlalchemy.url = sqlite:///db%%file.db
and it works.
I took the URL from your sample file and escaped thusly:
>>> urllib.parse.quote_plus("@#$%^&*()").replace("%", "%%")
'%%40%%23%%24%%25%%5E%%26%%2A%%28%%29'
added to alembic.ini as:
sqlalchemy.url = postgresql://test:%%40%%23%%24%%25%%5E%%26%%2A%%28%%29@localhost/test
and while I dont have that username setup, I pdb'ed into the psycopg2 dialect and we can see the arguments restored correctly:
{'host': 'localhost', 'database': 'test', 'user': 'test', 'password': '@#$%^&*()'}
We should probably add a note to the docs and to the comments inside the sample alembic.ini that indicates the above recipe for escaping.
If you quote
'@#$%^&*'>>> urllib.parse.quote_plus('@#$%^&*') '%40%23%24%25%5E%26%2A'so there is not naked
%As zzzeek said, because alembic uses configparser the "%" has to be double escaped into %%s for it to be valid. This isn't obvious either in the documentation or in the error message. I think it would be helpful if they were updated to make this clear.
Also, as the example repository shows, I'm just using Alembic and sqlalchemy to reproduce this error.
Indeed, I had not noticed that configparser was involved in this. Usually there is no need to double escape the %
What is the status of this issue? Still does not seem to work for me. Is there a workaround until the proposed fix is rolled in?
There is no fix propoed, just some additional documentation. Have you tried following this? https://github.com/sqlalchemy/alembic/issues/700#issuecomment-636037036
I did. The problem is the @ in the password. Delimits there and treats the rest as the beginning of the db host part and bombs with this stack trace. @ramki88 has a fix that was submitted above which has not been approved yet so I was wondering what the status of that was..
Traceback (most recent call last):
File "alembic/stamp.py", line 47, in
Where the XXX is the part of the password that follows the @ in the password string.
This result was after escaping the whole password string and replacing the single %s with doubles.
On Thu, Mar 7, 2024 at 12:58 PM Federico Caselli @.***> wrote:
Special characters need to be escaped. Like in the linked comment..
— Reply to this email directly, view it on GitHub https://github.com/sqlalchemy/alembic/issues/700#issuecomment-1982739512, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACEC3QFQVTH2DFULRT6N6FLYXAJKXAVCNFSM4NOBFVKKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TCOJYGI3TGOJVGEZA . You are receiving this because you commented.Message ID: @.***>
Only username and passwords should be escaped.
This is OT for this issue though. How to use a password string is explained in the sqlalchemy docs https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls