[jiggawatts]

Fantastically.

The new one had "hand rolled cryptography", which should make you twitch uncontrollably if you know anything about security.

The new application had, among other failings, hard-coded (unchangeable!) RSA keys used for communication channels. As in, all customers shared the same keys. I can't remember the exact specifics, but I swear at some point there was something like encrypted JSON in XML. Or was it encrypted XML in JSON? Does it matter which?

The old app that I wrote would happily take JavaScript or SQL snippets as inputs to any text field and do The Right Thing.

You don't want to know what happened to the new app when it was tested with malicious inputs.

The testing team were told "not to go too hard on it", because that would "derail the project".

[Winsaucerer]

Horror stories like this are always fun to read.

I've become a fan of avoiding ORM's and API's between front end and back end for websites. Want a page that shows a dashboard of xyz? Write the right query that fetches exactly what you want, render the HTML, and return it.

Super simple, and abstractions are at a great minimum. No SQL->ORM->API->frontend, each with their own twist on how they model the world. A splash of JS (perhaps via HTMX or Alpine), and this can take you a long way.

[ratww]

A few years ago I start a dashboard project that was mostly raw SQL.

I then saw the team wanting to convert it to ActiveRecord, which they started. But lots of queries had to use AREL (Rails' "low level SQL AST abstraction"), since they weren't really possible or just too difficult to do in ActiveRecord.

But AREL is so incredibly unreadable that every single AREL query often had its equivalent in plain SQL above it, as documentation, so new people could understand what the hell it was doing.

In the end some junior was unhappy with the inconsistent documentation and petitioned that every query, simple or complex, AREL or ActiveRecord, had to be documented using SQL above the AREL/AR code.

Then they discovered that documenting using Heredocs rather than "language comments" enabled SQL syntax highlighting in their editors.

After that we had both: heredocs with the cute SQL and some unreadable AREL+AR monstrosity right below it.

I still laugh about this situation when I remember it.

[Winsaucerer]

Another great horror story, thanks for sharing :)

[omegalulw]

> The old app that I wrote would happily take JavaScript or SQL snippets as inputs to any text field and do The Right Thing.

Confused here. Where were input validation checks in your implementation? How did you guard against SQL injection, etc?

[Winsaucerer]

Presumably they just did whatever the standard provided mechanisms for their SQL driver were (such as parameterised queries). User inputs text in a comment box, and you insert it into database using such a mechanism and it's safe.

And if you're using, for example, Go's templating library, then it automatically escapes everything in HTML templates unless you explicitly override this default behaviour.

[paulryanrogers]

Well if it was only 100 lines of plain JS then how would one guard against reflection attacks? I.e. submitting HTML (like script tags) then getting that to render when others view the tainted data.

[Winsaucerer]

Because on this way of building sites, the user submitted data is escaped before it reaches the browser. E.g.: https://go.dev/play/p/MmNSxU5QfAb (hit run to see the output).

The JS wouldn't need to do any escaping, because it's not trusted to handle any unescaped data. It's operating on the already-escaped html template.

[skeeter2020]

They certainly weren't using Go, or as stated, any framework. Also no mention of any type of web server; not sure what magical code was creating dynamic HTML from the database. Where was the business logic? Stored Procedures? No mention of more dynamic functions... No integrations... Sure sounds like a desktop browser-only app while the majority of the world today wants some mobile functions from almost every system.

There is a lot information, which is understandable but also conveniently supports a very unflattering narrative while simultaneously promoting the OP's awesomeness.

[Winsaucerer]

I think you're reading them far too strictly. I don't think they literally meant they were using nothing beyond JUST the SQL Server and then somehow getting HTML out of that, with 100 lines of JS on top. Unless I misread, I don't see anything that implies they weren't using something like PHP or ASP, for example.

[P5fRxh5kUvp2th]

To me this is the hilarious part.

Q: "how do they use the workarounds needed to secure the more complex approaches?"

A: "those security concerns don't exist in the approach, no workaround needed. That's part of the simplicity".

It just represents a fundamental misunderstanding, but it's not their fault, they've never seen anything else. Like someone using a JWT instead of a session cookie.

[eurasiantiger]

>Like someone using a JWT instead of a session cookie

Those aren’t mutually exclusive, you know.

[thow232329]

Just put the queries in procedures with parameters. Only store the procedure calls in your backend, disable arbitrary queries completely in your database permissions.

[skeeter2020]

> The old app that I wrote would happily take JavaScript or SQL snippets as inputs to any text field and do The Right Thing.

I can't be the only one here who is both skeptical and a little turned off by someone who says "You can stick any user input into a database query and you'll be fine", with a condesending pat on my head.

Your comments continue to be incredibly one-sided and biased. The summary is "My work was perfect and the new system a steaming pile". Perhaps this contributed to your replacement.

[P5fRxh5kUvp2th]

Do you understand what SQL Injection is?

Fundamentally it's mixing data and executable code such that the DBMS cannot properly distinguish between the two and can inadvertently treat data as executable code.

Parameterize queries very explicitly tells the DBMS "this is executable code, and this over here is data". Nothing anyone puts in the data will ever be mistake as executable code by the DBMS. THIS IS SAFE.

[eurasiantiger]

It is only safe for the SQL server. An injection attack could still be targeting a cache (to poison it with e.g. a malicious script), the browser (to steal data via XSS/CSRF) or the user (show an error message telling them to contact malicious number).

[P5fRxh5kUvp2th]

What the person said

> "You can stick any user input into a database query and you'll be fine"

Besides which, pretend SQL Server is a glorified cache, the result is the same.

[RockingGoodNite]

> I can't be the only one here who is both skeptical and a little turned off by someone who says "You can stick any user input into a database query and you'll be fine", with a condesending pat on my head.

Like how Google has worked the past 2 decades? OP said snippets then you gloriously paraphrased it into a completely different statement.

[barrucadu]

That's what parameterised queries are for. SQL injection isn't hard to avoid.

[2devnull]

“ was it encrypted XML in JSON? Does it matter which?”

I’m sure there were meetings where it was discussed at length and the stupidest idea prevailed, because other peoples’ failures are more useful than shared successes in such an environment. And probably for “security reasons.”