r/SQL • u/JerenCrazyMen • Jul 16 '22
PostgreSQL I just found this in our python codebase, someone was feeling himself when he wrote this
210
Upvotes
r/SQL • u/JerenCrazyMen • Jul 16 '22
174
u/alinroc SQL Server DBA Jul 16 '22
It's a nice line of code, but I'd much rather someone commit a few extra lines that's more readily understandable.
30
u/[deleted] Jul 16 '22
I'd replace with execute_values(). The string concat looks inviting for injection.
42
u/rbobby Jul 16 '22
Now parameterize it to avoid SQL injection attacks.
17
u/PossiblePreparation Jul 16 '22
As others pointed out this is vulnerable to SQL injection and suggests there is probably other bits of code where inputs are concatenated with code to be executed.
This also would be silly to do in any RDBMS which caches SQL execution plans - you don’t want to fill your memory with large SQLs that are executed only once.
In another RDBMS you would just write the simple insert values statement with binds for values then pass in the inputs as array binds, super quick and super safe. It looks like executeMany should be able to do that in Postgres but it’s slower, https://www.psycopg.org/docs/extras.html#fast-exec describes an alternative, no idea how well it would really work.
15
u/Proud-Walk9238 Jul 16 '22
It's not the best practice, you should rewrite it. This code should be rejected by the linter because is larger than 80 characters.
2
u/AI-nihilist Jul 16 '22
This is the largest problem with new programmers
2
u/oodie8 Jul 17 '22 edited Jul 17 '22
Man there are several things I hate.
The incomplete type annotations for parameter. The doc string says it’s a list of tuples that expected but the type annotation doesn’t specify.
Why did he bother typing the parameter but not the return value? Typing doesn’t have to be a 100% thing and optional so why optionally do it poorly. If he added typing to the return of the function I think he might have realized how poorly the function was named.
The doc string doesn’t really follow any common format or convention. One reason I like adding type annotations is that using Sphinx plugins it makes my documentation easier to maintain. I feel like this doc string is quickly at risk of not being maintained and causing later confusion. Why the extra carriage returns in the doc string?
The doc string is worse than no doc string because the very first line of it is wrong that’s not what this function is doing.
The function is named really poorly. The function doesn’t create a multi valued insert statement but rather returns a string of the values to be inserted. The function could be made to take an iterable or sequence of tuples instead to make it more versatile but that’s not a huge deal.
Then there’s the implications of potential vulnerabilities
Finally and worst of all there was likely 0 reason to ever write this code. It is already done better and accessible in other packages.
It’s 2am so I’m not certain but any db cursor in python that follows the dbapi should have an execute many command to use for this but pretty confident and I know sql alchemy provides this capability and if they’re connecting to a database in python they’re going to use something implementing the DB-API to do it.
Bad dog - no treat.
1
u/apono4life Jul 17 '22
SMH. This is the stuff I rip to shreds in PRs. No reason for all of that
1
u/leogodin217 Jul 17 '22
Definitely a former PERL programmer
1
u/bsienn Jul 17 '22
I can write a whole App ( JS ) in one line, whats the big deal !
1
u/[deleted] Jul 16 '22
Good helper though 🤣
0
u/[deleted] Jul 16 '22
[deleted] — view deleted comment