Voice of the DBA

Tag: software development

  • Securing Code Early

    Last year I started to get alerts from Microsoft Repos that someone had put a piece of security information in their code that pertained to one of my Azure services. At first I was worried, but then I realized this was the public version of AdventureWorks we maintain in Azure. We’ve published the login so people can test code against this if they want, and I started ignoring the warnings. Well, not ignoring. I still glance over them to verify the issue, but I’m less concerned.
    That doesn’t mean that you shouldn’t be concerned about sensitive information in repos. I saw this quote: ” Bots are crawling all over GitHub seeking secret keys, a developer served with a $2,375 Bitcoin mining bill found.” This follows a sentence that says “It once caused Uber to leak the contact details of 75m users“. These are from an interesting look at a way to secure code that might leak API keys. The idea is that you secure code on local commits and prevent secrets from being stored in your VCS.
    That’s a great idea. Can we prevent passwords in SQL code or ASP.NET config files? Can we actually start to teach developers to use secrets and other run-time mechanisms and prevent them from hard coding anything into a VCS? Perhaps, but we have a lot way to go and certainly more tool chains need to be updated to prevent what is a simple, but common, mistake.
    We need to get better at security, and we are. I see more Static Code Analysis tools being used in all sorts of companies, and I’m glad when they start to impact developers. While I get that changing your favorite method of writing a query or procedure is a pain, often we can reduce potential problems by enforcing some standards and avoiding poor coding practices. We do need to have exceptions since a rule for code might really be a general guideline 99% of the time with a few edge cases.
    Catching issues early in development is one of the goals. Having things like inline SCA in SQL Prompt (live demo), or continuous testing in Visual Studio, scanning with Sonarqube, and other tools are improving our code, while allowing us to build applications faster. There is plenty of other work to be done, especially for database code, but we are improving as an industry.
    If you haven’t used any of these tools and you write code, start learning a bit about them. You’ll likely appreciate their benefits once you get over the learning curve. You’ll also start writing better code.
    Steve Jones

  • Building a Database Engine

    I never wrote a database engine, unless you count writing code to read, write, and update flat files. I remember doing that early in my life with a friend as we tried to build our fantasy baseball game system. We needed a way to handle data, and use flat files, setting tokens to denote various “rows” and columns of data. I’m not sure that’s much of a database, but that’s the most I’ve done in building an engine.
    Someone decided to tackle building a SQLite clone in C and wrote a series of articles on the process. They are an interesting read as the author works his way through adding new functionality for the engine. While I wouldn’t want to actually recreate a database engine that I needed, the exercise is interesting. My C knowledge is a little rusty, but I can follow along enough to appreciate the way that the application takes shape.
    Writing software is often a challenge when we are creating a new system from scratch. It is often easier when we are trying to copy something that already exists, but there is still an effort to recreate all the functionality that already exists. However, it’s a good exercise and one that often helps software developers build stronger skills and practice their craft.
    If you were going to practice writing some code, database or otherwise, what would you like to write? Would you attempt a database engine? Many of us know how quite a bit about how SQL Server works, but I don’t know if we’d actually want to recreate the code for some part of the system. When I learned how the memory-optimized tables were structured, I had fond memories of building similar linked lists in university, though at a far simpler (and less efficient) level.
    I find myself tackling some problems to help others, or teach them a technique, but I haven’t had to build a full set of software in years. Maybe I’ll find a project at some point that I really want to tackle and actually build something larger. Finding the spare time to tackle a project is hard, but I’m ever hopeful that I’ll make the effort at some point.
    Steve Jones
    Listen to the podcast at Libsyn.

  • Computer Disruptions

    I travel a fair amount for work to speak at various events around the world. Traveling can be quite a disruption to my life, and I do work to limit the amount of time that I’m gone. As my kids have gotten older, I’m have less commitments at home and an extra night during a trip isn’t as much of a burden. It’s even enjoyable when my wife can accompany me to enjoy a few days visiting another part of the world.
    I still get on airplanes enough that the scheduling matters. I often plan trips to limit the number of hours I spend in transit, which means I depend on the airlines to keep to their timetable. I’ve been lucky that I haven’t experienced many delays, usually localized to a specific airplane, but I do worry when I see reports like this about computer glitches for the airlines.
    There are numerous computer systems that airlines share, some of which are still running on large mainframe systems. As the travel industry has grown, the software managing it has changed. From a single system (and company) coordinating all flights to a distributed set of applications that must interact and share data.
    Do we think this is going to happen more frequently in the future? Is the state of software development improving enough to prevent large scale issues and disruptions? As much as I’m pleased by the advancements in software development and the higher quality of code, I still think that companies often take too many shortcuts, without enough testing and evaluation in their quest to build systems quicker.
    I hope nothing goes too wrong as I’m traveling this year. I’m sure I’ll have a few delays, but if I do, hopefully I don’t miss any of the speaking engagements.
    Steve Jones

  • Code Building Code

    The dream for some people is to have an Artificial Intelligence (AI) system that you use to describe some requirements and it will build an application that meets your needs. Certainly some AI and ML systems have reduced the need to write code for portions of an application, but I don’t think there is any AI framework that can build an entire application from scratch.

    I was reading a blog post recently on using metadata in our database to produce a CREATE TABLE statement that could hold the output of a query. It’s not AI, but this is something I’ve done in the past, using code to help me get work done.

    Excel was one of my earliest helpers and still is. I find myself sometimes using Excel to build a series of statements that follow a pattern, but the contents of which might be based on some result set. A common example is a set of inserts based on some data. I use values in cells to build up a final statement and then copy these to SSMS or another tool and execute them. It’s quick and dirty, but it works well.

    In the past I have written code that would build other object code, usually to provide some API constructs for developers. In a few environments, we have had some standards about how to structure tables, views, and stored procedures, including at times an API-like standard that required certain functionality be implemented in stored procedures. Using a code writing stored procedure allowed me to quickly ensure that the required stored procedures were created and modified as tables were added or altered. This also ensured that we kept all these changes in sync, without depending on my to review every part of the API.

    I don’t know that I’ll see a true AI system that we can give a few specifications to and have it build a system, but the more we implement standards and known structures, the more we can use code to help us ensure those standards are implemented in a consistent manner. Using templates in our work, such as powerful snippets in SQL Prompt, along with code analysis that looks for poor practices can help us write better applications. Even if it doesn’t do all the work, these helper tools certainly improve the quality of the code we do write.

    Steve Jones