Voice of the DBA

Tag: software development

  • 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.

  • Common Algorithm Concerns

    When we build software, many of us use the same algorithms to solve problems. We might choose a similar method for a quicksort or a lambda validation or a regular expression. For database work, your code for a running total (or other common challenge) is likely very similar to many other people. At least on the same platform. You might solve this differently in SQL Server and Oracle, but for the same type of database, many of us write very similar code.

    Actually, many developers might copy and paste an answer from SQL Server Central, Stack Overflow, or another site. I’m not sure if I think this is good or bad, as it’s a good idea to reuse code if it solves the same problem. If you copy it and don’t test it, that’s bad. After all, the code might not solve your slightly different problem if you don’t check it.

    In the modern world, if we build software for our business using an AI assistant, could our company be liable if we knew our competitors were using the same AI service? Is this any different than a human developer performing a copy/paste from SQL Server Central? I don’t think it is in many cases, though the same concerns about intellectual property might be present in either case.

    The concerns over AI seem murky in some sense, especially as the AI might “generate” code that isn’t directly available on some public resource. I do think that this is more of a collusion using a service than an algorithm. Still, in the hyper-connected world, where many of our applications might look to take advantage of some service instead of implementing it ourselves, this could be an issue.

    I ran across a piece that discusses a lawsuit about a common pricing algorithm being used by different hotels. In this case, it’s not that the developers at different hotels used the same code, but rather that the hotels used the same service from a company, which of course, used the same code for all their customers. Whether you think this is a valid lawsuit or not, this is the type of legal action that others might bring if two competitors ended up using the same AI service and developed very similar code that might behave the same way.

    I don’t think that AIs (at present) can actually develop new algorithms or solve problems in a new way. Instead, they predict the likely solution based on how they’ve been trained by similar scenarios. In that case, how concerned are we about how getting common solutions in disparate pieces of software? For most of us, I think we’ve be pleased that we have well-tested (hopefully) code that runs efficiently (again, hopefully) being re-used in many places. That would be better for most systems in the world.

    What isn’t better is if humans become more adept at specifying prompts and producing software without lots of specialized expertise. For many developers that might be average, or even slightly below average, this might cause them concern for the security of their position. With good reason, as labor is one of the most expensive parts of building software.

    Ultimately, just as with any other position, the best way to build a safe, secure career, is to continue to build your skills and produce value for your employer. That way it’s unlikely any AI will ever outperform you.

    Steve Jones

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

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

  • The Code Freeze

    One of the ideas behind DevOps is that we minimize the time between code commit and deployment to production. We want to avoid work-in-progress and bottlenecks to getting our software into the hands of customers. This has led a lot of companies to release more often, albeit with smaller sets of features. The total number of things delivered under DevOps might not be greater, but it often is more targeted to those things our customers want/need/use.

    However, the idea of releasing often means that we try not to stack up too much work before deploying it. What does that mean for holidays and the code freezes or no-deploy periods that many companies have? How do you implement a code freeze under DevOps?

    I read an interesting series on code freezes (partially paid content: part 1, part 2, part 3), and it looks at some of the implementations, data from surveys, and pros/cons of implementing a code freeze.

    Two things here. First, I like code freezes as it gives staff a break. Two, doing this in a way that doesn’t just shift work (and stress) is hard. Do you have code freezes or deployment restrictions at your company? Do you like them?

    The post has lots of metrics from various companies, some known, some anonymous. It seems that for those companies using code freezes, they help employees take a break, but not everyone takes a break, which means some work is done and merges after the code freeze are problematic. For those who don’t mandate a code freeze, less work is done when people are gone, but some work continues and is released.

    I think the natural flow of work is that when fewer people are available, less work gets done. Poor management (or a hero complex) might overload the staff still working, but that doesn’t work long term. It might work now, given there is an oversupply of tech people and fewer jobs, but when things change, people will leave those positions, and lower quality, or at least less knowledgeable people, will cause problems for those firms.

    I’m not a fan of code freezes, but I am a fan of understanding that staff needs a break. Lots of people want to take time off during holidays, especially those with children who want to celebrate or travel. Others might be fine working and want to catch up on work, refactor things, or maybe address tech debt. I’ve been in both situations, and I hope organizations can adapt to both situations, though without sacrificing quality. We still need good code review and testing, and if there isn’t the staff to do those things, then delay merging code.

    The one thing that stood out to me was the learning that changing the pace is good for humans. If you’re in the habit of releasing every week (or day or whatever), moving to a different cadence for some time is good for you. Just as high-pressure work can’t be sustained for a long time, very slow periods aren’t good. However, both have their places as a change of pace. A high-pressure situation might be necessary to meet a business goal (or solve a problem). A low-pressure time might be a good time to think, experiment, and perhaps innovate.

    Or just fix and refactor some poorly designed technical debt.

    Do you want a time when you aren’t trying to get code to production, either as a dev or operations person? Or do you like to have your job predictable and work in the same pattern year-round? Let me know today.

    Steve Jones

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

  • Re-platforming

    Re-platforming is the process of moving a system to a new platform. Imagine taking an ASP.NET/SQL Server website and moving to Azure Functions on Azure SQL Database. Or maybe taking a Java client/server app with Oracle and moving it to a series of microservices against MongoDB. Those changes could be a net benefit to your organization in the end, but they aren’t quick or easy. They’re often fraught with various challenges that can cause a lot of stress while creeping over budget.

    There’s a post that talks about some of the things you might think about if you embark upon a re-platform. Often this takes place when an organization is looking to modernize their tech stack. Quite a few of the technology DevOps success stories take place when the older structures are not maintainable, but also not able to handle increased workloads or performance requirements.

    It’s often said that a complete rewrite rarely makes sense. I think that’s likely true, but that doesn’t mean it isn’t something you should do. It says that you should rarely do it, and you need to pick a good time when your old application, database, network, or some combination of these can no longer meet your anticipated future growth demands. This is likely a debate that your organization should have every year or two and ensure that continued investment in the current platform makes more sense than a new one.

    Once you decide to move forward, it will be a journey, which means understanding how new and old systems will interact becomes important. Compatibility, at least for some time, is very important. I’d say this is especially critical for how your data systems will work together and what options you have to replicate/ETL/etc. the data between systems.

    Even if you plan to make a cutover from one system to another, data migration is fraught with issues. Anyone trying to ensure data moves cleanly from one place to the next should have strong checks, usually with automated tests, that evaluate if your data is moving correctly. Row counts aren’t enough, or not the only metric. Random checks of individual rows, as well as checks for outliers, should be a regular part of any project that will migrate data. Learn where you struggle to correctly transform data because there will be places where this happens.

    I don’t believe that wholesale re-platforming often makes sense. Leaving SQL Server to go to another database platform, or even when you might try to change the paradigm with NoSQL or a Data Lakehouse, often costs a lot. Getting a return after staff training, migration costs, and even delays from inefficiencies during the process often equals many years of licensing. I think slow migrations are a better idea, perhaps adding something like ElasticSearch or Redis, experimenting with a small data lakehouse for a particular purpose, and replicating some data to Neo4J for complex hierarchy queries. These evolving mechanisms give you the chance to experiment and learn about the process. It will help you understand if there is a positive ROI if you continue.

    In many ways the world of technology changes slowly. We have lots of new shiny toys, but many of them don’t magically solve your problems. There are always tradeoffs and without strong domain knowledge, you may have more unknown unknowns that reduce your chances of success. Re-platforming can work, but make the decision carefully and proceed methodically, not with haste.

    Steve Jones

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