It can be done... But not by most people. Are history proves it, time [0] after time [1] and time [2] after time [3].
It is simply not worth it, if you can make use of parameterized queries.
[0] https://web.archive.org/web/20161024090111/https://ico.org.u...
[1] https://www.nytimes.com/2014/08/06/technology/russian-gang-s...
[2] https://www.baltimoresun.com/education/bs-xpm-2014-03-07-bs-...
[3] https://web.archive.org/web/20150219165019/http://www.batblu...
But still you wouldn't say that every software written in C is automatically insecure. You have to look into the actual case. Just grepping for SQL-string-concatination or uses of sprintf doesn't say much.
With sql that's different, every sane sql client library supports some form of prepared statements out of the box and they are really simple to use. There is no reason whatsoever to do this by hand, except for people that don't know better.
Prepared statements work fine as long as you just have simple SELECT statements with a few WHERE values as parameters. They completely fail if you need to do any advanced SQL with anything dynamic. Like optionally add more sophisticated calculated properties to the SELECT fields, conditionally JOIN in extra tables, use any of the DB engine's more advanced XML or JSON parsing features in a conditional way, support choosing <, > or =, etc.
this is true
> they are really simple to use
this is not. The amount of boilerplate I've written to properly parameterize a common pattern like
SELECT * FROM FOO WHERE
FOO.SomeKey IN (/* user-provided list of chosen values goes here */)
is grotesque. list = ['bar', 'baz', "Robert'); DROP TABLE Students;--"]
Foo.where(name: list)
# Foo Load (0.3ms)
# SELECT "foo".* FROM "foo"
# WHERE "foo"."name" IN (?, ?, ?)
# [["name", "bar"],
# ["name", "baz"],
# ["name", "Robert'); DROP TABLE Students;--"]] params = ", ".join(["?"] * count)
template = "SELECT ... WHERE foo in (%s)" % (params)
# proceed to prepare a statement from the query template
2) use a subquery, insert data somewhere if necessary SELECT ... WHERE foo in (SELECT foo FROM foos WHERE some_value = 42)
3) if the maximum number of parameters is low, you can keep it variable by enabling parts of the query with boolean parameters: SELECT ... WHERE (test_param1 AND foo = param1)
OR (test_param2 AND foo = param2)
OR (test_param3 AND foo = param3)The sprintf example acknowledges that it’s not clear if the strings involved are user-controlled. It’s definitely possible that the programmer knows all the strings that may be involved and can prove that buffer overflow is impossible.
There’s the complaint that Zoom crash reporting reads registry values that the application has permission to read. This is only problematic because it sends that information to Zoom’s crash reporting service; i.e., somebody other than the user. But programs if the program can’t write sensitive data to the registry, then there’s nothing sensitive to disclose.
If message history goes into that database and is not sanitised properly there's a good chance this gives other conference participants RCE.
So, while that’s not about remote code execution, I will agree that what is actually logged in this SQLite database could be important information. But I would expect a little more than “OMG! They’re logging to a SQLite database, and you could log bad things to a SQLite database!”
* Bad SQLite queries can cause code execution
* Zoom is using concatenation to build queries
* Zoom uses SQLite to store message history and other remotely-controllable data
I've seen enough. I don't need to see the entire chain - which in this case would be that the message history queries specifically are built in an unsafe way - to know that Zoom is software that I absolutely should not trust, and that I do not want to have installed on any of my endpoints if I want the other data on them to remain secure.
Unfortunately, I do not have a choice. I am working with two telehealth providers who use Zoom. So the next best thing is to be as angry as possible about it. The hiring of Alex Stamos is a good move but we need to keep the pressure on.
Put another way, I work for an EU-based fintech. We have similar guidelines (plus a strict log rotation policy, partially due to GDPR but also for many other reasons), but i'm pretty sure our head of security would shit bricks if he found out that our logging framework had an RCE.
Zoom isn’t open source. The blog post doesn’t actually have the entire call chain for the potentially problematic code. It doesn’t determine if the strings have been sanitized before the string concatenation, or if they come from a known limited set, etc. It’s entirely possible that the concatenation is in the lowest levels of some enterprise-specific framework code that is guaranteed to only be called after relevant safety checks have been run.
We have a blog post that points to unconnected facts, says “I can imagine evil ways to connect these facts, but I’m not going to spend any more time determining if my concerns actually exist in the rest of the codebase.” We can decide how uncomfortable that makes each of us. I will say that SQLite is used in the most surprising places. I’m almost certain it’s used by whatever browser you’re using right now. If you refuse to use software that relies on SQLite until you can verify that every call site is safe, you’re going to lose a lot of sleep.
Of course, I’m influenced by my background. I still see stories about software installed without management knowledge, and while I remember working at places with lax enough policies that was possible, I haven’t had that kind of access on my work computers since before the Sony Pictures hack in 2014. I’m not in finance, but I’ve written software for people who are. In the US, financial compliance departments care about the software traders and financial advisers use for their jobs, and any software installed on the same computers (this was an issue at one of my jobs because our software generally wasn’t on the approved list, so our customers had to have it installed on a separate computer for unapproved software; and that was apparently common practice in the industry). I don’t lose any sleep when my kids use Zoom on their school-provided laptops, but I personally can’t use it for work purposes because my work laptop is locked down, and I expect it to be locked down.
Every time it's "hey mr senior developer, I'm trying to reproduce that slow query but I can't, even though I'm doing the same thing as the application" and I'm like "you must be doing something wrong" and sure enough: no, it's the SQL server doing something wrong. And every time we found out "oh, running it un-parameterized makes it fast". So then I have to talk down the juniors who are like "well fine, I can fix it by running it unparameterized, let's just do that!".
So then we have to figure out how to trick it to generating a non-idiotic query plan without abandoning the security that parameterization provides.
... basically I'm deep into "the emperor has no clothes" on MS SQL server in general.
It is extremely frustrating. Never ran into problems like this with Postgres or MySQL.