4 ms·

But why would you ever send a plaintext password into a sql query?

by ipython 3y ago

civilized 3y ago
Because you are lazy, irresponsible, and/or incompetent.
AndroTux 3y ago
Or anywhere else except a hash function for that matter?
petee 3y ago
Since being clever is often a footgun, I'll admit to one idea I had years ago; I never really gave it the smell test, and it only existed for a personal project. But if one person thought of it, others may have as well. I was learning to write sqlite C functions for fun, and thought since storing plain passwords has been such an issue, why not offload the responsibility from the programmer and let the db handle it fully -- consume plaintext, salt, hash, etc, and could transparently upgrade algorithms when necessary. Luckily I've learned enough to recognize that my skillset is not of the level necessary to fully evaluate the risks. Today the idea makes me feel uncomfortable.
- paulmd 3y ago
  one general problem with this (and also audit logging) is making sure that such changes can't be rolled back. I think the consensus the last time I looked at it was to do it in a separate connection or batched at the end of the main transaction. but audit logging is just such a tremendous pain in general and everyone's got their own specific requirements. I think in general you would not want to do this in the database anyway (definitely not unless it's sharded and not really even then). You are taking that hashing load off your application servers and moving it inside the DB after all. When your DB is out of CPU, it's out, they're tough to scale. And in SQLite you are holding the DB write lock for longer, which serializes every other thread that needs to write. That's also true of holding connections/resources in traditional DBs. It's notionally fine for toy problems and you can probably do "clever" shit like sharding uuids across multiple files by hash to scale a bit further. But increasingly, even as someone who likes the idea of a "richer" interface to RDBMS, and doesn't mind writing SQL functions/triggers/views where it makes sense, I think the database really just not is the place for unnecessary gizmos on performance grounds either. Don't put hashing in your DB.

But why would you ever send a plaintext password into a sql query?

civilized 3y ago

Because you are lazy, irresponsible, and/or incompetent.

AndroTux 3y ago

Or anywhere else except a hash function for that matter?

petee 3y ago

Since being clever is often a footgun, I'll admit to one idea I had years ago; I never really gave it the smell test, and it only existed for a personal project. But if one person thought of it, others may have as well. I was learning to write sqlite C functions for fun, and thought since storing plain passwords has been such an issue, why not offload the responsibility from the programmer and let the db handle it fully -- consume plaintext, salt, hash, etc, and could transparently upgrade algorithms when necessary. Luckily I've learned enough to recognize that my skillset is not of the level necessary to fully evaluate the risks. Today the idea makes me feel uncomfortable.

paulmd 3y ago
one general problem with this (and also audit logging) is making sure that such changes can't be rolled back. I think the consensus the last time I looked at it was to do it in a separate connection or batched at the end of the main transaction. but audit logging is just such a tremendous pain in general and everyone's got their own specific requirements. I think in general you would not want to do this in the database anyway (definitely not unless it's sharded and not really even then). You are taking that hashing load off your application servers and moving it inside the DB after all. When your DB is out of CPU, it's out, they're tough to scale. And in SQLite you are holding the DB write lock for longer, which serializes every other thread that needs to write. That's also true of holding connections/resources in traditional DBs. It's notionally fine for toy problems and you can probably do "clever" shit like sharding uuids across multiple files by hash to scale a bit further. But increasingly, even as someone who likes the idea of a "richer" interface to RDBMS, and doesn't mind writing SQL functions/triggers/views where it makes sense, I think the database really just not is the place for unnecessary gizmos on performance grounds either. Don't put hashing in your DB.