Databases

database1_s600x600

Exiting script mid-way – gracefully (with a die-file)

0

Why I never thought of this before, I don’t know. But this is saving my bacon right now:

The scene:

I have a script that does 10 000 things on each loop on my DB. Unfortunately, because of the architecture that I’m interacting with, I can’t wrap the entire thing in a transaction. If I could, then killing the script mid-way will have no negative effect on the db, because it’ll just roll back the whole thing.

However, killing the script in the middle having it non-transactionalised, introduces a whole range of negative side-effects – life for instance inflating the summary data I’m building.

Anyways, forget the above, it doesn’t matter.

All I want to do is to “tell my script” that I’d like it to quit after the current iteration of the loop.

My solution: Give it a “die file”.

Say for instance my script’s name is “maintenance20150216.php”

I put the following code inside it:

Cool thing about this? All I got to do while the script is running:

This in turn creates the “die file”, which tells the script to quit after the next iteration of the loop.

This way I don’t have to kill or ctrl-c the script and risk it quitting at the wrong place inside the loop. My script can run for it’s calculated days of processing, and if I need to make a change – or at the very least have it back off for a bit to give the DB a breather, I can do so easily and safely now.

database1_s600x600

alter table … change column… NOT NULL DEFAULT 2 — weird behaviour

2

>> EDIT:

So, thanks to @morgo and @cmptrwizard I now know what was going on :)

Turns out, old decisions do sometimes come back and bite later on hehe… who knew self-deprecating-sarcasm.

We turned STRICT_TRANS_TABLES off way back when we upgraded to MySQL 5.6 – meaning that the error MySQL was supposed to through due to the alter not going to do what I was hoping (my mistake), was not thrown. This resulted it the failure being silent – which is what bugged me for days :)

Thanks guys for the assiste.

<< EDIT

Original post follows…

This is just weird, and it’s bugging me. It appears when MySQL does the alter table operation on a previously nullable column it does run through it changing the current null values to the new default – but instead of using the default specified it uses the base default value of the core.

Have a look at this to reproduce it:

Ok, let’s add some rows, so we have a nice view of things:

Looking at the data, we find it looks like this:

Ok so far so good. Now, let’s alter the table’s “some_value” column to be a default of “2”.

Now, when doing this on a large table it’s pretty clear that MySQL actually runs through the table entries and changes their values (as it takes minutes to run on a big enough table). So we know it’s not just changing the metadata of the table.

Right, let’s look at the data again:

Do you see what I see? The “some_value” of row with id 101 was changed to “0” – zero. But the alter table statement said the default should be “2” – two.

Ok, did I break the table? Let’s test inserting a row without the “some_value” filled in – which in the past would’ve made it null… but now that I altered the definition it should make it “2”.

Ok cool, so let’s retrieve the data again:

Anybody know why the heck this is?

Binary Matrix Security

Warn about Security Holes, but don’t be an asshole

0
The view of the world from the perspective of the Developer having the Security Problem:

We’ve all read about security, password encryption vs password hashing, why encryption is better than clear text, why hashing is better than encryption, why bcrypt is better than say sha1, and so on. But the fact of the matter is, is that most projects on the outset de-prioritises these security concerns for other, “more important” things at the time, like for instance getting the job done and launching the project.

“Sure, we’ll look at security after launch” is most commonly used among developers, and yes even though this is the wrong thing to do and we all agree that it’s the wrong thing do to, a lot of us if not most does exactly this.

The problem with this is, however, that we never “get around to it” when it comes to fixing these things. The reasons for it is three-fold. Either we’re lazy. Let’s not do that when it comes to security. The other two reasons are more understandable though. We’re perhaps just too darn busy. For the business factor we have to actively as professionals in the industry make an effort to “make the time” for these important “behind the scenes” work – especially when it comes to security, but this is a whole different discussion.

The other more common reason is “because it’s really difficult and we’ll likely have to change our client base’s behaviour”. This is tricky, because a lot of times either the company you work for has a management team that’s really great at marketing and business, but don’t really care that much about security (or perhaps know that much about it). One can convince them to take the plunge by pointing out the marketing or PR nightmares if we get hacked, but it’s still difficult. Also, most of our client-base are really not phased about security, in fact they don’t even know what it is until one of two things happen: 1) you change something and they notice or 2) you get hacked and they notice because it’s in the local newspaper (or news site). Both, unfortunately, bad, because both create phone-calls and support tickets, generally which you have enough of and would like to not create more admin by changing something.

Anyways, so let’s all agree that it’s the right thing to do to make the security change required and change the behaviour of the clients. The sooner the better, but now that you’re 6 years into a project it’s going to take some time. It’s not only going to take time, it’s going to be difficult to juggle security and ease-of-use to make sure that the clients don’t complain too much. My advice: Plan! Plan how you’re going to attack the problem, make sure you talk to your internal security specialists (or external consultants (or both)) to make sure you’re doing the right thing. Then lay out your time-line and try to stick to it as close as possible.

All said and done, as Developer ultimately it’s your responsibility to protect your client’s data, including and very importantly, their passwords.

The view of the world from the perspective of the “Guy who notices the security hole”:

Now, to get to what the subject of the post is about. Let’s switch our role from being the developer who’s charged with fixing the security problem, to some random client (or prospective client) who’s using the system/site/service or about to sign up for it.

From time to time we as internet users come across security holes in systems. The world is full of security problems as discussed above. But for all the security problems that we notice there’s almost always somebody behind that who really care about fixing that problem, but whom are faced by the difficult task of doing so (accompanied by all the human and technical problems surrounding it). And furthermore, in this group there’s a sub-group of developers who are in the planning phase or the active development phase of fixing the problem. This is where my story resides.

With the modern web of social media everywhere, we tend to talk a lot… a LOT… online. We generally tend to talk more about the things that we’re passionate about and especially that we know more than others about (well, we hope so anyways). Some of us are developers and some of us are even security specialists, even working for a security consultancy firms. Thus we’ll tend to talk about security problems. However, we need to be careful about when we talk about it, how we approach the discussion, and how public the discussion is.

Let me use an example of what happened a while ago:

Our team was faced with a security problem (not quite as big as the one described above, but still complex and tough to fix) for quite some time. We’ve spend a great deal of time and effort planning how we’ll be fixing the problem. Development on fixing the problem was about to start, in fact the morning of the below described incident, we had a water-cooler-chat where it was agreed that development on the problem is starting and we’ll finish the initial phase of it in about 2 weeks (hey, a nice agile scrum sprint!)

Then the “unthinkable” (read “annoying thing”) happened:

Now, we’ve had security concerns raised by clients and prospective clients numerous times. Most companies in the IT industry do. Every few weeks we get the odd ticket where a client is concerned about a security problem on our system. Usually it’s a small security hole that we plug and make sure it exists nowhere else on our system. But every few months we get the more serious concern, to which we thank the client and assure them that we’re looking into it (which we do/did). This time, though, the concern broke on a very public way – on Twitter. Yes, you can just imagine the storm this kicked up! Think about it… on twitter, for everybody to see, a concern was raised about the security of our system.

Now, this is not the first time it happens in this fashion, but what’s annoying is that the individual who tweeted it should’ve known better – he/she is a “professional security consultant”.

The wrong assumptions of it was made on twitter which made the problem look even worse to the public and untrained eye (no we didn’t store passwords in clear text, but this was said on the internet, which naturally makes it true *sarcasm*). Now, as everybody knows, the people running social media on an organisation is extremely good at what they do; that said, they’re not security specialists. Thus, starting a discussion/fight on Twitter (on twitter!!) with a company about security means you’re fighting/discussing a matter with somebody who is ill-equipped at answering your questions properly. The team running the social PR did exactly what they were supposed to do, they tried to mitigate the public problem by ensuring the tweeter that we’re serious about security, and that they will take it up with the relevant department. Still the tweeter attacked the problem (publicly). The tweeter proceeded to paste a link to an article about it. Again the social team assured him that it’s being looked at. Yet, the tweeter again explained that we’re being stupid (yes, I agree we were stupid) and that this is “security 101” (ok) – still, publicly… on twitter… with a company of thousands of followers. Then the social team took a different approach, to which the tweeter baited them by asking something along the lines of “So, I can assume that you’ll sort the problem out?”.

Let me categorically state: We were in the wrong here. When it comes to the question around security, we did the wrong thing. But that’s not what the issue is that I have here.

What scares me, is that we have a so called professional, a person who knows what the effects of talking publicly about security, whom broadcasts the fact that this security hazard exists (and even makes it seem to be a bigger hole than it actually is). Let me put it another way: This person basically SMS’d (Texted) tens of thousands of people about the security vulnerability. Think I’m being too harsh? Read the definition of twitter: “Twitter is an online social networking and microblogging service that enables users to send and read “tweets”, which are text messages limited to 140 characters. Registered users can read and post tweets but unregistered users can only read them. Users access Twitter through the website interface, SMS, or mobile device app.[read more here]

Luckily, nothing bad happened, however the carefully planned timeline and actions that we were going to take over a 2 week period went out the door. Thousands of rand in money of the time and resources spent planning, and the first part of the development was flushed down the drain. All because there was a public announcement of the security hole.

Upon inspecting the user, I noticed that she/he did not contact us about this in a private manner whatsoever. There was never a phone-call from the user. There was never a support ticket created about the problem. Nothing at all. The first time this user interacted with us was when the tweet was made.

This, sir/madam, is called being an asshole.

Conclusion

So, the moral of the story, to all my colleagues and friends and readers of this blog, if you spot a security vulnerability… by all means, notify and warn the company about the problem. Because face it, we’re all in this together, and we’d like our industry to get better at what we do, and we’d like to see security holes to go away. Heck, even make a fuss about it and cause some hairs to raise with throwing a few choice-words around over the phone until you speak to somebody who gives a damn about security. But for the love of world peace… don’t… be… an arse and post it on twitter/facebook/google+/linkedin/whatever social media you procrastinate on.

If you’re so concerned about the company’s security and they don’t do anything about it, then take your business elsewhere if they don’t listen. But really, think before you post, don’t just have verbal (or keyboard) diarrhoea.

Side-note: The security problem was “plugged” with a bad hack, and then fixed properly afterward – but only a fraction of the ease-of-use features that we were planning to build in to it was implemented thus hurting innocent clients, because… well… once again, our window has closed – and now that the security hole was plugged, we no longer had a burning need to work on the cosmetics of this project, there’s other more important things to work on.

So, the tweeter in question wanting to help the industry (well I hope so) by publicly posting a security vulnerability to get the company’s attention, but actually ended up just hurting the industry.

Anyways…

… happy surfing and posting… and remember…

… don’t be an asshole :)

database1_s600x600

Transposing Rows into Columns with MySQL

0

This is very basic, but figured there might be people out there that would find this useful. Say you have a table with signups to your site, and you want to chart these signups. However there are three different signups: guest, paid, pro. Let’s say, for argument sake you have a table with 2 columns, date and signup type, like below:

If you had to run the following group by query you’d get the results row by row:

… resulting in the following output:

Let’s say, however, you want to have each date in one row, yet you want to have a column for guest, another column for paid and yet another for pro with the resulting counts in them? Well that’s easy, here’s what you do in SQL:

… and then you have output like this:

What’s awesome about doing it this way, is that you could copy this into Excel or whatever the tool is you use and line chart these figures against each other for a nice visual representation of your signups (or whatever you use this for) :)

Let me know what you think!

Go to Top