4 ms·

Aside of the obvious SQL injection (heck - the way this is set up, it obviously even takes multiple queries in one string - this is pure heaven from an injectio

by pilif 10y ago

Aside of the obvious SQL injection (heck - the way this is set up, it obviously even takes multiple queries in one string - this is pure heaven from an injection standpoint), I'm also wondering what kind of schema this is? Creating an user as a sequence of operations? Why? Imagine the ways this data can get corrupted? What if for some reason only one of the queries executes? What if somebody removes one of the rows for a user but not the other when doing support or maintenance?

Macuyiko 10y ago
In a past life, I converted an intranet application for some government department from Oracle/Coldfusion. The whole application, every SQL query taking input, was vulnerable to SQL injection. For many insert operations, I'd find a pattern appearing of first inserting a row with some random value in one of the columns (`temp_key`), then performing a select based on this key to get the row back to know the primary key, and then continue to update other fields in the same row, and adding other records to other tables referencing this primary key. Obviously, transactions was a foreign concept to the original developers. I still remember taking a deep breath once I found a code snippet that would try an insert again if the insert failed because the randomly generated `temp_key` was already used before, a problem that seemed to really start bothering them as the database grew larger...
- delgaudm 10y ago
  I'm stretching my memory as a "full stack" dev back in the 90s but I seem to recall that CF couldn't do transactions for its first few versions. People were begging for <cftransaction> to become a thing. I also don't remember being able to call an oracle stored procedure from CF in version 1 or 2.. But I'm hazy on that for some reason I remember the ODBC driver not supporting it. As a result I recall resorting to hacky stuff like what you describe. It wouldn't surprise me in the least if there is government cfml code out there from the 90's. It was a challenging time with far fewer tools.
- INTPenis 10y ago
  That is how I learned to do SQL when I was learning how to code in ASP 2.0 and PHP 17 years ago. It seemd quite natural then and for several years onwards.
tfm 10y ago
RDF on the not-so-cheap? Real-time profiling of the internal Factory code by keeping track of every attribute and running a bunch of exciting INSERT hooks? I have seen this pattern before, it comes when somebody is sick of having to adjust their database schema every time somebody comes up with a new user attribute that they absolutely definitely need to keep audited. Now you can add whatever "columns" you like, boss!
parenthephobia 10y ago
It smells like a RADIUS database. Certain authentication methods for RADIUS require a cleartext password, sadly, which will be stored in the Cleartext-Password attribute.
huuu 10y ago
I had to think about the database design of ProcessWire (PHP CMS). It uses one table for identifiers and field tables for all data connected to the identifier. To store a user with a username, a password and an email address it uses 3 tables. One for the username (identifier), one for the password and one for the email address. The only safe way to insert a user is by using transactions.

by pilif 10y ago

Macuyiko 10y ago

In a past life, I converted an intranet application for some government department from Oracle/Coldfusion. The whole application, every SQL query taking input, was vulnerable to SQL injection. For many insert operations, I'd find a pattern appearing of first inserting a row with some random value in one of the columns (`temp_key`), then performing a select based on this key to get the row back to know the primary key, and then continue to update other fields in the same row, and adding other records to other tables referencing this primary key. Obviously, transactions was a foreign concept to the original developers. I still remember taking a deep breath once I found a code snippet that would try an insert again if the insert failed because the randomly generated `temp_key` was already used before, a problem that seemed to really start bothering them as the database grew larger...

delgaudm 10y ago
I'm stretching my memory as a "full stack" dev back in the 90s but I seem to recall that CF couldn't do transactions for its first few versions. People were begging for <cftransaction> to become a thing. I also don't remember being able to call an oracle stored procedure from CF in version 1 or 2.. But I'm hazy on that for some reason I remember the ODBC driver not supporting it. As a result I recall resorting to hacky stuff like what you describe. It wouldn't surprise me in the least if there is government cfml code out there from the 90's. It was a challenging time with far fewer tools.
INTPenis 10y ago
That is how I learned to do SQL when I was learning how to code in ASP 2.0 and PHP 17 years ago. It seemd quite natural then and for several years onwards.

tfm 10y ago

RDF on the not-so-cheap? Real-time profiling of the internal Factory code by keeping track of every attribute and running a bunch of exciting INSERT hooks? I have seen this pattern before, it comes when somebody is sick of having to adjust their database schema every time somebody comes up with a new user attribute that they absolutely definitely need to keep audited. Now you can add whatever "columns" you like, boss!

parenthephobia 10y ago

It smells like a RADIUS database. Certain authentication methods for RADIUS require a cleartext password, sadly, which will be stored in the Cleartext-Password attribute.

huuu 10y ago

I had to think about the database design of ProcessWire (PHP CMS). It uses one table for identifiers and field tables for all data connected to the identifier. To store a user with a username, a password and an email address it uses 3 tables. One for the username (identifier), one for the password and one for the email address. The only safe way to insert a user is by using transactions.