Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Mistakes Beginners Make When Working with Databases

craigkerstiens.com

Post reply on HN
Print view View original

191–200 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#191

Post by lloyd-christmas »

mathattack wrote: post #189
lloyd-christmas wrote: post #177

Earlier quoted context omitted.

> The assumptions behind this then gets built into the code base This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a fea…

> And even if you fix the database, you are missing the historical hierarchies I'm not following this one. Are you referring to an audit trail? No - meaning if update the database schema, data will be missing that wasn't collected properly the first time. (If you didn't think you needed customer hierarchies, you didn't create them as customers came in) I hear you on avoiding over-generalizing. That creates problems t…

> data will be missing that wasn't collected properly the first time

Maybe I'm nitpicking but even if you had that field in the database, it's still up to the application to collect it (unless it's something like a timestamp, but that's just a dumb mistake regardless of how you design your database)

Re: Mistakes Beginners Make When Working with Databases

#192

Post by mathattack »

lloyd-christmas wrote: post #191
mathattack wrote: post #189

Earlier quoted context omitted.

> And even if you fix the database, you are missing the historical hierarchies I'm not following this one. Are you referring to an audit trail? No - meaning if update the database schema, data will be missing that wasn't collected properly the first time. (If you didn't think you needed customer hierarchies, you didn't create them as customers came in) I hear you on avoiding over-generalizing. That creates problems t…

> data will be missing that wasn't collected properly the first time Maybe I'm nitpicking but even if you had that field in the database, it's still up to the application to collect it (unless it's something like a timestamp, but that's just a dumb mistake regardless of how you design your database)

Yes - I think we both agree.

Re: Mistakes Beginners Make When Working with Databases

#193

Post by sopooneo »

brianwawok wrote: post #139
muxxa wrote: post #113

Earlier quoted context omitted.

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

Okay so you run a school and want to make a students database. What do you use a a PK? 1) Social security #? Fails when you have international student. 2) Last Name, First Name, Middle Name? Fails when you have a repeat name. 3) Last Name, First Name, Middle Name, Home Town, Start Year? I guess this works for most of the time. Now you need to join to this table from the classes table. So now you need all 5 keys dupli…

I have actually done this for a classroom management app I made mostly for fun when I taught high school. At first I wanted to use the district issued ID number as the primary key for each student. Except some students came and started classes and needed attendance records before the district got around to issuing them their official numbers. So I fell back to auto-increment int as the primary key, with their "district_id" nullable, default null, but unique constrained as just another field equivalent to their first_name.

Re: Mistakes Beginners Make When Working with Databases

#194

Post by bmm6o »

hinkley wrote: post #179
_RPM wrote: post #51

Earlier quoted context omitted.

The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"

Right, but generally this has not been the default behavior. If you're lucky and your chosen libraries were written by wise people, it's a couple lines of code and a few tests. But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.

It also depends on your environment. On a platform like .Net it's not impossible to get your JS serializer to serialize 64-bit ints and floats as strings while keeping 32-bit and smaller numeric. So no matter how you represent it server-side it will survive a round trip. For a language that doesn't make that kind of distinction in the type system (I'm thinking Python, but I could be wrong), you really have to toString it right when you get it from the database. The only other option is to base the serialization decision on the value, which means sometimes your ids are returned as ints and sometimes as strings. At best it's ugly, at worst it leads to bugs.

Re: Mistakes Beginners Make When Working with Databases

#195

Post by hinkley »

bmm6o wrote: post #194
hinkley wrote: post #179

Earlier quoted context omitted.

Right, but generally this has not been the default behavior. If you're lucky and your chosen libraries were written by wise people, it's a couple lines of code and a few tests. But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.

It also depends on your environment. On a platform like .Net it's not impossible to get your JS serializer to serialize 64-bit ints and floats as strings while keeping 32-bit and smaller numeric. So no matter how you represent it server-side it will survive a round trip. For a language that doesn't make that kind of distinction in the type system (I'm thinking Python, but I could be wrong), you really have to toStrin…

If you get to the point of sniffing, then a slightly better option might be to suffix all your foreign keys with something like 'key' or 'ID'. Or use slugs for everything.

Or you could initialize all your tables with the first primary key being 2^32 and only lose one quarter billionth of your legal keyspace.

Re: Mistakes Beginners Make When Working with Databases

#196

Post by sopooneo »

VLM wrote: post #164
sshykes wrote: post #78

Earlier quoted context omitted.

Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.

password reset email #123 gets url /user/123 password reset email #124 gets url /user/124 password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type. This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Wel…

Shouldn't this risk be mitigated with authorization rules? Or do we assume we are delivering pages without any type of auth first?

Re: Mistakes Beginners Make When Working with Databases

#197

Post by drumdance »

sshykes wrote: post #78
drumdance wrote: post #74

Earlier quoted context omitted.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.

Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.

Yes, of course that's what needs to happen. And that's what I do when I'm the one doing the implementation.

But a junior dev just out of code school doesn't necessarily think of this. So when I ask one to build the basic scaffold and db schema I say "make sure you use UUID," then later I show them how security holes like this can manifest.

I've seen this security hole so many times in other sites that I feel like it's a good first principle to limit "guess-ability" in the schema wherever possible.

Re: Mistakes Beginners Make When Working with Databases

#198

Post by drumdance »

vectorpush wrote: post #110
drumdance wrote: post #74

Earlier quoted context omitted.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.

> IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. IMO, explicitly prohibiting unauthorized access to an API endpoint is a basic security tenant, not a "holistic" one. if iterating through an API's integer key sequence results in unauthorized access to data, replacing the integers with UUIDs only masks the problem and I'd say is a classic example of…

Yes it is a basic security tenant.

But sometimes you're working with a legacy API and/or a bad auth mechanism.

Not every project is greenfield or is maintained by senior devs.

Re: Mistakes Beginners Make When Working with Databases

#199

Post by dennisgorelik »

sopooneo wrote: post #196
VLM wrote: post #164

Earlier quoted context omitted.

password reset email #123 gets url /user/123 password reset email #124 gets url /user/124 password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type. This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Wel…

Shouldn't this risk be mitigated with authorization rules? Or do we assume we are delivering pages without any type of auth first?

You should allow to reset password to the users without authentication (and therefore without authorization).

That's the nature of password reset link.

Re: Mistakes Beginners Make When Working with Databases

#200

Post by dalyons »

brianwawok wrote: post #178
olavgg wrote: post #150

Earlier quoted context omitted.

I use Varnish, so it is never a performance issue.

Does Varnish preload your images from cache? What if 100 people hit the page at the same time for an uncached image? I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...

Varnish will actually hold those 100 connections open while it dispatches 1 single request to the backend. When that request returns (assuming the cache control headers allow it), it will serve that one response to all 100 requestors. It's a very powerfull tool for anything that's even slightly cachable.
Post reply on HN