Voice of the DBA

Tag: software development

  • Thinking About Technology

    Technology has dramatically changed the world over time. The advent of cars dramatically changed the US, as people could go places and meet others in a way that was difficult and slow before. The telephone let us communicate with people all over the world at a pace that was previously impossible. Computer technology has furthered this at a truly amazing pace, especially since the adoption of mobile devices by so many people. The flexibility in how we can integrate computer technology into our lives has been incredible.

    However, each technology change brings about plenty of negatives and potential problems as well. I ran across a piece from L. M. Sacasas that has some questions we might ask about any technology, including the software we build. The start of the piece is that most of us don’t think about how our work might be misused, which can lead us to dismiss security risks or moral misuse risks. We often don’t consider the malicious ways people view applications.

    The piece is interesting to read, but it ends with several questions that we might ask ourselves as we build something. I think a lot of these questions might not apply to our work with databases or corporate technology, but some do. I think many of them might apply if we think about the tools we use, especially AI.

    Most things many of us build are re-hashes of something else. We might smooth the flow of work with better UX in an application. We might rewrite code to more efficiently use resources. We might implement features or reports in response to a business request, but we often are lightly evolving our software not changing it. We do, however, build things that others might misuse, either accidentally or maliciously.

    The list of questions is interesting, but I also think we need to consider that others might use your system differently, not just from a UX perspective. Consider how they might exploit your software to achieve other aims. We may not be security experts, but others are experts and there are plenty of tools available to scan code and identify vulnerabilities. Use these tools with the knowledge that just because you wouldn’t use the software in a particular way doesn’t mean others won’t.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • A Programmer’s Attitude

    I caught this piece on the need for programmers (developers) to not trust anyone, including themselves. It is written by a software developer for other software developers, but I think it can also apply to database work as well. It is a bit long, but it starts with the nature of abstractions in the world and how they let us work with simpler models of a situation or environment. However, most abstractions are leaky, and our assumptions about them can cause our systems to fail.

    The leap from trust to abstractions seems a bit funny, but it makes some sense. We ought to simplify our situations so that we can generalize how to solve them, but we also need to verify things. There are a few examples, one of which is we ought to use tests to ensure the code does what we think it does, including using a wide variety of data. We ought to ensure that refactoring something doesn’t break the system, or more often for databases, we return the same results. Changing a query to run more efficiently with joins or a window function instead of a loop or subquery should return the same results. Not just for one row but all rows, and across different inputs.

    One I especially like is the check on deployments. If we changed our code and had a deployment, did our code actually get deployed? In today’s world where different people might be responsible for merging and deploying code than those writing the software, we might want to verify that our changes actually got deployed. Perhaps reviewing deployment reports or logs can help ensure that we know the state of our live systems in addition to those in development.

    There are some suggestions for how to become a better software developer, and as you might guess, this requires learning. I think in today’s world, some group exercises (katas) or reviews can be helpful as well. Maybe even practicing new techniques in a sandbox and running them through a PR process to let others see them. It can be scary to ask others to review your code when you are learning, but they might teach you something, or you might teach them something. We build in teams; we should think about learning in teams, at least periodically.

    Becoming a successful software professional, data or application, takes regular work to improve our skills. Just as a woodworker might practice with their tools or a chef with recipes, we ought to practice with our tools. I’d hope that many organizations would also see this as something that needs to be encouraged with some amount of time allocated towards ensuring your staff continues to improve and produces high-quality results.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • The Cost of Rework

    I often deal with customers who are looking to improve the way they build and manage database software. These could be small companies or large enterprises, with teams of developers trying to enhance their application software to solve new business problems. Often those enhancements require new data, with the related schema changes in a database. Even if there isn’t any new data, often we need to query data in new ways, combing, filtering, aggregating, and otherwise transforming data into extra information that a business can use.

    Solving many of these problems is iterative by nature. We try one thing, then another. Often a developer might experiment with a data model or query, trying to match a requirement they’ve been given. Once they produce a solution, we may find problems in testing, or far too regularly, in production. These could be data-related issues, where the developer hasn’t considered values in their solution (zeros, blank or long strings, extreme dates, etc.). These could be logical errors, where the developer just made a mistake. There could also be a problem with the requirement, where the customer provided an incomplete or incorrect specification to the developer.

    In any of these cases, what often happens is rework. We need to re-look at the code we’ve written and change it in some way. This isn’t a complete rewrite, but looking for the logical error, incomplete algorithm, or poorly performing structures. Then we need to (test it and ) move the code back through our process of deploying to QA, UAT, production and anywhere else the problem exists. The further we are in the process (closer to production) the more costly the effort to rework code.

    Some rework is unavoidable, but I think quite a bit isn’t. If we wrote better-performing and cleaner code the first time and had a pattern for testing our code, we might avoid a fair amount of rework. If we caught silly mistakes early, we could get more done.

    I think that often developers minimize the effort required to perform rework. They think small changes are easy. After all, we already know the problem space, we’ve looked at it, and the fix should be quicker than the initial analysis and development.

    Or is it?

    One of the things I’ve seen is that everyone is busy. We are pulled in many directions by many different people and work on lots of different problems. Even when we work on the same application every week, it takes some time to remember the context under which we wrote the code. If it’s not our code, then we spend time trying to understand how the original developer approached the problem. Without good specifications or tests, we might not view things the same way during rework. We often fix one problem but create another.

    Automated testing should help here, as the tests should codify the understanding of the requirements, but too few database developers use automated testing for their code. Even developers who embrace testing from the application might not have sufficient test data to ensure their code will work in production.

    Most of us are expected to do more work this year than last year. We’re constantly battling a lack of resources. We can’t necessarily write more code, but we can learn to be more efficient. We can learn to reduce our rework by writing code better the first time. That takes some effort and knowledge. It takes working as a team, sharing successes and struggles, and adapting our code to use patterns and avoid anti-patterns. It requires some automation to embrace static code analysis and testing to avoid silly mistakes getting deployed and wasting time. More importantly, it takes support from management to spend time learning as a team to produce better code and avoid silly mistakes.

    A little investment in learning and practice goes a long way. Unfortunately, too many people, both staff and management, are unwilling to commit. Except in fixing bugs and spending time on rework. Far too many people see that as an regular, accepted part of software development.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • SQL Injection Is Not Acceptable

    SQL Injection has been a problem for my entire career. Thirty years ago I could have easily just blamed this on ignorance, as most of our developers didn’t think about the nefarious ways that hackers enter data in our applications. These days, there isn’t a good reason for this to keep happening, and the problem is us. I think that we don’t provide good examples or training on secure coding or secure architecture as a normal part of teaching programming. In many organizations, we don’t check for issues and prevent their release. Some do, but many don’t. On top of this, the existing code is usually a poor template for writing future code. I do think Microsoft aims for secure coding in SQL Server but in Windows, there is work to be done there.

    A few months ago, I saw an article that noted the US CISA organization and the FBI issued a secure-by-design alert (PDF) that noted there is no excuse for SQL Injection vulnerabilities (SQLi) in modern software. This alert notes that SQLi has been an “unforgivable vulnerability” since at least 2007. Inside the document on vulnerabilities, it notes that a single quote can’t be used in certain fields: username, password, ID field, or numeric field. They also note that co-mingling user data and query data, like constructing queries on demand, is a poor practice.

    The alert even emphasizes that developers are engaging in poor practices when they “fail to treat user-supplied content as potentially malicious.”

    I agree, and their recommendations are worth reading and implementing. If your boss doesn’t want to spend time on these, point out the bulletin and note that since this is a published advisory, I wouldn’t be surprised to start seeing lawsuits in the US or even insurance claim denials if your software team doesn’t follow these practices. Note that the list includes leadership support of secure coding and secure design principles.

    I doubt this has changed a lot, but I think some managers likely see this as a) a good idea, and b) a way to mitigate potential issues down the road. Changing the habits of software developers, updating code snippets or patterns, and even adding linting/static-code-analysis to CI pipelines take time, as does the training for developers. However, it’s something that has to start changing over time to get better at building higher quality, more secure software.

    I’d like to see insurance companies refuse to indemnify or cover losses or problems from software that is written from this point forward and is vulnerable to SQL Injection. There are far too many tools out, and software is too critical to allow these types of simple coding errors to proliferate. I’d also be pressuring companies to ensure older code is being actively refactored to reduce the number of vulnerabilities over time for all software they still support.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.