使用“喜欢” 在带有 python (django) 参数的游标/查询中

发布于 2024-07-14 23:06:47 字数 392 浏览 7 评论 0原文

我知道这可能很愚蠢，但我决定无论如何都要问。

我一直在尝试查询类似的内容：

 cursor.execute("select col1, col2   \
                    from my_tablem \
                    where afield like '%%s%'
                    and secondfield = %s
                    order by 1 desc " % (var1, var2) )

但是我在类似的句子中遇到错误。它不喜欢额外的 %，我需要额外的 % 来获取包含第一个 %s 值的所有结果。

有想法吗？

蒂亚！

原文

I know this may be something stupid but I decided to ask any way.

I've been trying to query something like:

 cursor.execute("select col1, col2   \
                    from my_tablem \
                    where afield like '%%s%'
                    and secondfield = %s
                    order by 1 desc " % (var1, var2) )

But I get an error in the like sentence. It doesn't like the extra % which I need to get all the results that contains the first %s value.

Ideas?

TIA!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无人接听 2024-07-21 23:06:48

首先，为什么不使用 Django ORM 来实现此目的？

MyClass.objects.filter( aField__contains=var1, secondField__exact=var2 )

其次，确保您得到了您期望的 SQL。

stmt= "select... afield like '%%%s%%' and secondfield = '%s'..." % ( var1, var2 )
print stmt
cursor.execute( stmt )

第三，您的方法存在一个称为 SQL 注入攻击的安全漏洞。你真的不应该像这样执行 SQL。

如果您绝对必须在 Django ORM 之外执行操作，则必须在查询中使用绑定变量，而不是字符串替换。请参阅 http://docs.djangoproject.com /en/dev/topics/db/sql/#performing-raw-sql-queries。

First, why aren't you using the Django ORM for this?

MyClass.objects.filter( aField__contains=var1, secondField__exact=var2 )

Second, be sure you're getting the SQL you expect.

stmt= "select... afield like '%%%s%%' and secondfield = '%s'..." % ( var1, var2 )
print stmt
cursor.execute( stmt )

Third, your method has a security hole called a SQL Injection Attack. You really should not be doing SQL like this.

If you absolutely must do things outside Django's ORM, you have to use bind variables in your query, not string substitution. See http://docs.djangoproject.com/en/dev/topics/db/sql/#performing-raw-sql-queries.

回复收藏 0 原文

蓝颜夕 2024-07-21 23:06:48

可以将字符串“%”破解为搜索字符串吗？

var1 = '%' + var1 + '%'

then query normally:

cursor.execute("select col1, col2 
                    from my_tablem                     where afield like %s
                    and secondfield = %s
                    order by 1 desc " , [var1, var2] )

can hack string '%' into search string?

var1 = '%' + var1 + '%'

then query normally:

cursor.execute("select col1, col2 
                    from my_tablem                     where afield like %s
                    and secondfield = %s
                    order by 1 desc " , [var1, var2] )

回复收藏 0 原文

空心↖ 2024-07-21 23:06:48

我有类似的问题。我试图在串联的名称字段中进行搜索。我的查询是这样的：

sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = '%%%s%%'"""

User.objects.raw(sql, [q])

问题是 %% 破坏了我的查询。我最终得到的解决方案是：

q = '%' + q + '%'
sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = %s"""

User.objects.raw(sql, [q])

I had a similar issue. I was trying to search among concatenated name fields. My query was something like:

sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = '%%%s%%'"""

User.objects.raw(sql, [q])

The problem was that the %% were breaking my query. The solution I wound up with was:

q = '%' + q + '%'
sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = %s"""

User.objects.raw(sql, [q])

回复收藏 0 原文

眼眸印温柔 2024-07-21 23:06:48

Persona.objects.raw("**SELECT** id,concat_ws(' ',nombre,apellido) **AS** nombre_completo **FROM** persona **GROUP BY** id **HAVING** concat_ws(' ',nombre,apellido) **ILIKE** '%s' " % ('%%' + query + '%%'))

（Postgresql 9.1）

Persona.objects.raw("**SELECT** id,concat_ws(' ',nombre,apellido) **AS** nombre_completo **FROM** persona **GROUP BY** id **HAVING** concat_ws(' ',nombre,apellido) **ILIKE** '%s' " % ('%%' + query + '%%'))

(Postgresql 9.1)

回复收藏 0 原文

~没有更多了~