Updating PostgreSQL JSON fields via SQLAlchemy

If you’ve found this article, you may have discovered that as of PostgreSQL 9.3, there’s no immediately obvious way to easily (for some value of “easy”) update JSON columns (and their fields) in place like you sort of can with HSTORE when using SQLAlchemy. Supposedly, PostgreSQL 9.4 may be adding this feature, which means we’ll have to wait some time thereafter for support to appear in psycopg2 and, by extension, SQLAlchemy.

But, that’s okay, because it turns out that updating JSON isn’t really a big deal after all. The only thing needed is to snag the JSON column as an object, update it, then pass it as a value for the parent column in an update. It’s that easy.

Here’s a contrived example using the Python shell:

 
# Grab the object.
>>> config = session.query(Config).filter(Config.id == 1).first()
>>> config.values
{u'allow_username_change': True, u'initial_group': u'new-user'}
 
# Change it.
>>> config.values["allow_username_change"] = False
 
# See? Changed.
>>> config.values
{u'allow_username_change': False, u'initial_group': u'new-user'}
 
# Now do the update...
>>> session.query(Config).filter(Config.id == 1).update({"values": config.values})
1
>>> session.commit()
 
# Check our values again.
>>> config = session.query(Config).filter(Config.id == 1).first()
>>> config.values
{u'allow_username_change': False, u'initial_group': u'new-user'}

The maddening bit about this was how long it took me to figure it out! But hey, I like to share. Hopefully this will save you some time.

No comments.
***

SQL and PHP

We have a few things to talk about regarding SQL and PHP. It’s short and sweet, so I’ll be brief.

I’ll start with saying that I’d like to put this tactfully, but unfortunately, I don’t know how else to say this:

It’s 2014 and we’re still not using parameterized statements.

Seriously.

I ran across a project that was linked from one of the various “discover Github” mailings I’m subscribed to that looks very promising. It’s called Lychee, it looks great, and the demo functions well. I can’t think of any other open source gallery systems at the moment that look as good as Lynchee (or as simple). There’s just one problem: It’s an absolute haven for SQL injection attacks. Gosh, I don’t even know where to begin. Nearly every single query that accepts some form of external input is a potential landmine of shame, sadness, and roasted pandas. Worse, the last ticket addressing this particular issue was posted about a month ago. If there’s no activity this weekend, I might just go through and convert it to use parameterized statements myself and offer a pull request.

The other problem I have with it is the code formatting. It’s pretty ugly. It certainly wouldn’t pass PSR-2, and it reminds me vaguely of the ad hoc formatting found in most other popular PHP products (commercial and otherwise, so it’s not like Lychee is unique). But, that’s okay. Code formatting is mostly subjective, as long as you’re following some standard (even your own) and remain consistent. While some of us would rather split hairs over various stupid things, it’s mostly incidental because we care about appearance. Appearance does matter, because care and consideration for how the code looks probably reflects how the code operates. Pragmatically, though, appearance doesn’t matter that much. It can be fixed with automated tools. No biggy.

However, the SQL injections are unforgivable.

There are hundreds of resources on the matter, many targeting PHP. So it’s not like there’s much of an excuse. Heck, I’ve written about this in the past, and I’m not even a smart dude. If you haven’t heard of parameterized statements, either you’ve been living under a rock or you’re new at this.

The good news is that if you’re new to the big bad world of web software, don’t fret. The absolute best place to get started if you’re writing PHP is to read PHP the Right Way. It includes dozens of tips and tricks, information on established and emerging standards, and delves into a few advanced topics for larger applications. I would highly encourage you to read the entire thing. In particularly, I would suggest reading the section on databases, because it covers PDO, SQL injections, and parameterized statements (often known as prepared statements). There’s nothing wrong with the MySQLi extensions per se but new PHP code should use PDO instead. PDO even has a lesser known feature to inject queries into objects, which can take care of some of the more mundane work (if you happen to swing that way).

Don’t get me wrong. It’s great to see new projects popping up all the time challenging older, more established ones. It breathes new life into under-served markets and creates a greater body of useful tools for all of us. Sometimes a fresh look into old concepts helps us think outside the box, questioning established design principles, and jarring us back into the reality that there might actually be a better way of doing things. Please don’t feel this post is an outright criticism: It’s not. We all had to start somewhere. If you’ve never heard of SQL injections, well, now you have. Extra information in your arsenal of tools won’t hurt, but it certainly will help combat one of the most common design flaws in software today.

What you should take away from this post is that there are unsavory individuals out there who are deeply interested in easy-to-exploit flaws in software. These folks gain a great deal of enjoyment from imposing misery on others, either by writ of outright damage or embarrassment. Because of them, we have to develop for the web differently than we might have, say, 10 years ago.

So let’s start by teaching the newcomers not to repeat our mistakes!

No comments.
***

nginx v1.4.5 and IPv6

I recently updated the VPS this blog is sitting on. Coincidentally, this also updated nginx to the latest version and broke everything. I didn’t think much of it at the time, but when I linked a friend to this post over on my fun blog, he was delivered to the default nginx page. Puzzled, I poked around for a while, mostly examining DNS records and server configurations. I couldn’t find anything wrong.

Then I had a eureka moment.

I’m on IPv6 at home. I have this site (and others) configured to use IPv6. It hadn’t occurred to me until then that it might be protocol related. Using curl (curl -4 and curl -6), I confirmed my suspicions. Although the server was listening on TCP and TCP6, it was only serving up the vhosts on IPv6 and not IPv4. IPv4 was receiving the standard welcome page.

I knew that I had configured the server appropriately for both stacks. I’ve read through the docs. I combed through dozens of blog posts documenting the process. I was convinced the server was correctly configured. I must’ve fiddled with it for a good hour or so, reviewing documentation and the likes to no avail.

Infuriating.

Since nginx 1.2 or 1.3 (I can’t remember precisely), it’s been necessary to add ipv6only=off to the listen directives in order to support a dual stack environment. It’s my understanding this trick doesn’t work on some BSDs, but I know for a fact it worked fine under Linux. Or so I thought. I tried it successfully under Arch and Ubuntu with identical results with the exception that I neglected to recall one minor detail: My Arch install updated to nginx 1.4-something well after I had configured my desktop for developing on a dual IPv4/IPv6 stack. I suspect it’s probably broken in the same manner. But, I use it strictly for development, so I’m not particularly concerned whether or not it works on IPv4. I don’t use the protocol much within my network, so why worry, right?

To continue: I decided to take another stab at it and discovered something curious. Previously, all that was required to enable dual-stack support in nginx was to add the following to whatever was configured as the default host

    listen [::]:80 ipv6only=off default_server

And then all subsequent vhosts simply required

    listen[::]:80;

That’s all. It used to work–like magic. But, sadly, magic eventually runs out. This is why electronics stop working once you let all the “magic smoke” escape. Sorry, it’s an old electrical engineering joke my father has oft repeated. I guess it’s brushed off onto me.

Anyway, here’s the solution. You might find it contrary to some of the antiquated information out there lurking on various blogs dating back from 2011 through the middle of 2013. It works for nginx 1.4.5 (and possibly earlier versions), but the trick is to add this to the default vhost configuration

    listen[::]:80 ipv6only=on default_server;
    listen 80 default_server;

And for all subsequent vhosts

    listen[::]:80;
    listen 80;

I should note it works fine without adding the ipv6only=on directive, just like the generic vhost config (above). I believe I’ve read that this is because the default behavior enables ipv6only automatically. However, if you’re running a slightly older version, you might need to keep it. Hence why I’m not going to remove it from my examples. Better safe than sorry, right?

default_server is (hopefully) obvious, but only required if you want to provide a default site (or page) for users hitting your web server’s IP. Or for ancient browsers that haven’t been taught how to use the Host header. Are there any of those left?

So, the trick is that you need two listen directives. Period. Yes, even for TLS/SSL. If you skip these directives on any vhost, the missing protocol binding will be skipped for that vhost. I suspect this is probably documented somewhere. The problem though is that there are literally dozens of blogs pointing the old instructions that used to work. These are now deprecated. Following them will only lead to sadness.

Initial frustration aside, I find meshes well with my preferences. It’s more explicit and there’s no question which protocols nginx will use when binding to the configure port or ports. However, it will cause headaches for IPv6-enabled sites migrating from nginx 1.2. So, if you’re running Ubuntu and have decided to update in order to gain access to newer features (websocket support, SPDY, et al), expect breakage. More importantly, be absolutely certain you’ve independently tested all of your deployed sites using IPv4 and IPv6. Make liberal use of the -4 and -6 switches for curl. It’ll save you from unpleasant surprises.

No comments.
***
Page 1 of 4712345102030...Last »