Voice of the DBA

Tag: static code analysis

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

  • Trust But Verify

    Npm is the default package manager for the Node.js runtime. Most of us have heard of of Javascript, which is used extensively in writing applications. When developers write code using this framework, they often include code from other packages and developers, just referring to the functions they need. This allows applications to build on the work of others, without having to re-implement functionality that is commonly used.

    Many languages have libraries that are available for use, but most require that a developer download them, include them in their software and then publish the resulting software. In many node.js applications, the developer does some of this, but when they publish the application, the users pull down the versions of the packages that they need at that time. This allows developers to avoid including a lot of code in their applications, reducing file sizes, bandwidth, etc.

    Recently an issue arose with a popular package that is included in many applications. I first noticed this on Twitter, then saw it called out in a Visual Studio User Group meeting and then even more online. A programmer made some helpful changes to the package and was given rights to make more by the maintainer. This user when altered the package to include some malware that would attempt to steal bitcoins from users that ran an application using the package.

    This is much different than how our T-SQL code is structured, with all the code contained inside the database. There are some exceptions, but for the most part we can look at all the code that will be executed as a part of our batch. That doesn’t mean that we aren’t responsible for reviewing and checking out code.

    This might not be easy in the Javascript world, but it should be easy(ier) for us. I know the warnings about testing code exist, but I’m always somewhat surprised when users ask questions on the SQLServerCentral forums, and then can’t understand why simple code (select count(total) from mytable) doesn’t work on their system because they don’t have a mytable. That causes me to think that too few people actually look at code before they run it. If they don’t check a 10-30 line set of code, do they ever check something as complex as Ola’s script? Likely not.

    Part of our duty as professionals is to be careful with code that we get from others, run it in a sandbox, test it, make sure this is code that will work well for us. Not many of us can download code from the web and have it run on our SQL Server without modification, but if we’re asking questions on a forum, we might just do that. If the problem is complex and the code is large, we might not pay enough attention. As SQL Server expands to get code in R, Python, Java, and more, we may need to be more diligent in scanning code to look for problems such as data leakage.

    Can you imagine getting some Python code from the web that should break strings apart into works and find out that somewhere in the complex class structures this code also sends a copy of your data to some malicious website? I can, and it’s why I’d be very careful vetting code on the data platform.

    Steve Jones

    The Voice of the DBA Podcast

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