There are many variants of SQL. DB-API only solves for so much. From "[Python-ideas] Draft PEP on string interpolation" (=> f-strings) https://groups.google.com/d/msg/python-ideas/6tfm3e2UtDU/8ugqbZtYAwAJ : ```quote IIUC, to do this with SQL,
sql(i'select {date:as_date} from {tablename}'
needs to be ['select ', unescaped(date, 'as_date'), 'from ', unescaped(tablename)] so that e.g. sql_92(), sql_2011() would know that 'select ' is presumably implicitly escaped * https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization * http://docs.sqlalchemy.org/en/rel_1_0/dialects/ * https://docs.djangoproject.com/en/1.7/ref/models/queries/#f-expressions "Django F-Expressions" ``` And from the next message: ```quote [... Django F-Objects, Q-Objects, deferred evaluation due to schema dialects] Each ORM (and DBAPI) [has] parametrization settings (e.g. '%' or '?' or configuration_setting) which should not collide with the f-string syntax. * DBAPI v2.0 https://www.python.org/dev/peps/pep-0249/ * SQLite DBAPI https://docs.python.org/2/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#conjunctions
s = select([(users.c.fullname + ... ", " + addresses.c.email_address). ... label('title')]).\ ... where(users.c.id == addresses.c.user_id).\ ... where(users.c.name.between('m', 'z')).\ ... where( ... or_( ... addresses.c.email_address.like('%@aol.com'), ... addresses.c.email_address.like('%@msn.com') ... ) ... ) conn.execute(s).fetchall() SELECT users.fullname || ? || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) (', ', 'm', 'z', '%@aol.com', '%@msn.com') [(u'Wendy Williams, wendy@aol.com',)]
from sqlalchemy.sql import text s = text( ... "SELECT users.fullname || ', ' || addresses.email_address AS title " ... "FROM users, addresses " ... "WHERE users.id = addresses.user_id " ... "AND users.name BETWEEN :x AND :y " ... "AND (addresses.email_address LIKE :e1 " ... "OR addresses.email_address LIKE :e2)") SQL conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()
[(u'Wendy Williams, wendy@aol.com',)] ``` On Sat, Feb 22, 2020 at 12:35 AM David Mertz <mertz@gnosis.cx> wrote:
OMG, please no! Please, for all that is decent, do not use an ORM in any code I will ever need to look at!
The SQL injection attack is just silly if you don't run arbitrary strings. Don't ever do that. But running a query that is hard coded as text, with just a few parameters filled in (the DB-API is handy) is good... yes, check those parameters for sanity and permission first.
On Fri, Feb 21, 2020 at 10:39 PM Bruce Leban <bruce@leban.us> wrote:
On Fri, Feb 21, 2020 at 5:53 AM <minecraft2048@gmail.com> wrote:
The idea is to add a new string prefix 's' for SQL string. This string doesn't do anything in Python, unlike b"" or f"" strings, but interactive Python shells like IPython or Jupyter can parse the following characters as SQL syntax instead of Python syntax and give SQL syntax highlighting and autocompletion, and if they are configured correctly, they can do column name autocompletion. Unfortunately when I try to type s"select * from table" it gave me syntax error instead, so I think this need to be implemented in Python language itself instead of module
First, as to SQL specifically, writing literal SQL in code is a bad idea. It's easy to have bugs, especially sql injection. You should use an ORM at the very least a SQL builder. Instead of:
sf"select * from sometable where name = '{userName}'"
you would write something like:
sql.query(SomeTable).filter_by(name=userName).all()
And I believe the same thing applies to HTML and just about anything else that has a complicated enough syntax that this idea would be useful for.
Second, if I had a strong reason to do something like this, I'd want to use a function that enabled me to add run-time sanity checking (at least during development and testing phase):
_html_(f"This is a <b><i>{adverb} bad</b></i> example.")
and in production that function would just return the value untouched.
--- Bruce _______________________________________________ Python-ideas mailing list -- python-ideas@python.org To unsubscribe send an email to python-ideas-leave@python.org https://mail.python.org/mailman3/lists/python-ideas.python.org/ Message archived at https://mail.python.org/archives/list/python-ideas@python.org/message/GVTWOF... Code of Conduct: http://python.org/psf/codeofconduct/
-- Keeping medicines from the bloodstreams of the sick; food from the bellies of the hungry; books from the hands of the uneducated; technology from the underdeveloped; and putting advocates of freedom in prisons. Intellectual property is to the 21st century what the slave trade was to the 16th. _______________________________________________ Python-ideas mailing list -- python-ideas@python.org To unsubscribe send an email to python-ideas-leave@python.org https://mail.python.org/mailman3/lists/python-ideas.python.org/ Message archived at https://mail.python.org/archives/list/python-ideas@python.org/message/VRU4A7... Code of Conduct: http://python.org/psf/codeofconduct/