Voice of the DBA

Tag: software development

  • Better Static Code Analysis and Security Scans

    I was listening to a talk from Stefan Simenon on their CI/CD transformation within ABN AMRO, a large financial company. One of the interesting things he noted was that they consider open source to be less secure, possibly with more vulnerabilities than in house written software. Their build pipeline will fail if a developer starts using new OSS components.

    I find that interesting, as the DORA 2018 State of DevOps report sees more use of OSS software in companies that are adopting DevOps. In general, I think that having many people able to view the source and find errors makes companies feel that open source is more secure. I think that’s likely more true, though it’s a bit of a philosophical argument. We can look at some data, but it’s hard to prove that one or the other is empirically more secure.

    The thing I agree with is that using new components without some review is not a good idea. Whether this is written in-house, copied from an Open Source project, or purchased from a vendor, we need to perform some testing and analysis of the code or component.

    This is also true in database code. When we get a query from a developer, it’s often easy to determine what is happening, but when the size of code grows, or there is a large stored procedure, we often don’t perform a detailed analysis. What’s worse, we don’t have good static code analysis tools for database languages. As much as I like what Redgate Software has done with SQL Prompt, I know this is rudimentary and is built to avoid code smells. There isn’t any detailed look at whether the code is secure, or if there might be unintended effects.

    There aren’t really any good tools I’ve seen, though I’m not even sure what I’d want here. How can a tool tell me that querying 4 tables and updating 3 more is OK, but an insert to some other table in a separate database is bad. That insert to the other database might be what a malicious actor wants to copy data elsewhere. The best thing to me would be some analysis of what objects are being touched and how, which could help alert developers to potential issues.

    Building static code analysis tools for database languages is hard, but it’s something that our industry needs to do. This is even more true when we start to have more programmability features, like the ability to execute other languages inside of our database engines. In those cases, not only do we need to ensure the code for another language passes test, but that we understand what types of interactions our database code has with those modules.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Republish: Software Engineering in Practice

    Still in Hong Kong, or maybe on the way back. Hard to know with time zones. Read Software Engineering in Practice today.

  • Redgate Lives DevOps

    At Redgate, we release a lot of changes to our products. In fact, this is the “About Redgate” slide I’ve been using in talks related to the company.

    2018-08-23 09_36_29-ReduceAttackSurfaceArea.pptx - PowerPoint

    If you look in the lower right, you’ll see product releases from last year (2017). We have 30-ish products, so that’s roughly 38 releases per product. Not every product releases at this cadence, but a lot of them release every week. SLQ Monitor, for example, releases every Wednesday.

    Of course, there’s the inevitable release-a-bug-and-need-to-fix-it-so-a-second-release this week, but those don’t happen too often. I’ve been tracking releases this year, and not too many were corrected in the same week, but it does happen.

    Some people say that’s the problem with DevOps, but I think it’s the advantage. I guarantee that most software releases include bugs. If you release once a quarter, are you ready to re-release in a couple days to fix something? Or do customers live with issues for a quarter? The advantage of DevOps is we can fix things quickly, in addition to adding new features quickly.

    Redgate does some amazing development work and I’m proud of the ladies and gentleman that write the code.

    I still complain, and there’s room for improvement, but they do a great job and I try to remember to thank them and complement their work when I do see them.

  • The Master of the Schema

    “The database … should be the master of the schema”.

    It’s not often I see an application developer talk about the importance of the database, or at least that the database (and data) are very important to the success of the application, but in this case, that’s what I saw. The quote is from this blog, which talks about the problems of the code first approach to building an application. It’s from a Java development company, though it’s their jooQ product blog that actually generates Java code from the database, so they get the need to pay some attention to the database.

    The thrust of the article is that using an ORM is fine, but this isn’t necessarily the best way to design your datastore if you need a relational system. There will be mistakes made in naming objects, in structuring them and ensuring indexes exist and more. The longer you go with generated database code from some code-first type system, the more issues you might have later as you try to modify both the application and database, especially the database, since you can’t drop it and recreate it.

    If we could drop the tables and rebuild them, life would be great. Maybe we should keep copies of data on the clients, and just reload the necessary data after a deployment… It would make my life easier as a database professional, but I suspect this isn’t the best way to ensure data quality and consistency (not to mention completeness).

    I agree that if you’re building an application, you should go database first. At least start with some basic structures and learn to modify them. The practice you get modifying code, with scripts not GUIs, will be invaluable later to both developers and DBAs/admins. You’ll start getting practice and understand what it means to deploy scripts to your database. After all, making schema changes with scripts in your development database is the first “practice” you get for deployment. If you can’t get the scripts to work here, how would you have any confidence they’ll work in production?

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.