Voice of the DBA

Author: way0utwest

  • SQL Prompt Fixes Poor Order By Coding

    SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide you along and fix some bad code that your team might write out of habit.

    SQL Prompt 10.1 released recently, and one of the recent fixes is for an issue that we’ve denoted as BP002. This is where someone writes this code.

    SELECT arts.ArticlesID
      , arts.Title
      , arts.Description
      , arts.PublishDate
      , arts.URL
  FROM dbo.Articles AS arts
  WHERE arts.PublishDate > DATEADD(WEEK,-2,arts.PublishDate)
  ORDER BY 1

    I don’t see this a lot from Microsoft stack developers, but PHP, Java, and some other languages will do this.

    This has been deprecated, but it’s a poor practice as well. The dependency between the ordering and column list is not good. This is an easy place to make mistakes over time as code is refactored.

    If I do this, SQL Prompt gives me a green squiggly line below the constant.

    2020-04-02 11_49_05-SQLQuery7.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (63))_ - Microsof

    If I put the cursor here, I get a lightbulb in the sidebar. Clicking that will give you some options.

    2020-04-02 11_49_13-SQLQuery7.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (63))_ - Microsof

    If I click the first item, this will give me a placeholder where I can choose a column. We could replace this with the first column in the column list, but if you detect this during refactoring, the first item from the column list might not be the one you want.

    2020-04-02 11_51_20-SQLQuery7.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (63))_ - Microsof

    I can start typing and intellisense takes over.

    2020-04-02 11_49_29-CandidateList

    It’s a small thing, but this is a one way that increases code quality for developers that might not know better.

    If you haven’t tried SQL Prompt, download an eval and see what you think. If you have it, upgrade and ensure you have all the code fixes.

  • Daily Coping 15 Apr 2020

    I’ve started to add a daily coping tip to the SQLServerCentral newsletter, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to send a letter or message to someone you can’t be with.

    I’ve actually been doing this on a lot of days. I’ve been reaching out through text, Facebook, or email to various friends, family, and colleagues that I would often see at different times during the year, but I haven’t seen in awhile. It’s a quick way to just touch base with some people and see how they’re doing.

    I should just do this more often.

  • BYOD Fingerprint Concerns

    Many modern devices, whether mobile phones, tablets, or laptops, contain fingerprint readers. We’ve had readers for a long time, but they were very expensive and not widely deployed. That changed with mobile phones and now many devices include fingerprint readers as standard equipment. So much so that this seems to be a very common way of authenticating access on lots of devices.

    This isn’t necessarily a very secure way of protecting data. An ArsTechnica article notes that fake fingerprints were able to access these devices 80% of the time. That’s a success rate that might worry a lot of security personnel. Most organizations allow some sort of BYOD device, including many financial, medical, and other organizations that deal with sensitive data. Since control of a device often includes saved credentials and the ability to approve 2FA implementations, this could be an issue.

    Certainly nation states might try to take advantage of this to gain control of a device and access data from other governments, but I’m sure corporate espionage is in play here as well. I wouldn’t be surprised if this also becomes a technique for pranksters and jokers. Imagine you can bypass the fingerprint on a colleague’s phone. Maybe you want to change their wallpaper. Maybe you want to send an embarrassing email to friends. Maybe you want to add a backdoor to some code using their GitHub credentials. You could do anything from an innocuous joke to a malicious career threatening action. Imagine you choose to do this while your colleague is at lunch, with their phone forgotten on their desk.

    No security is perfect. Many of us that work with someone often might be able to guess a PIN or pattern on a device if we see it often enough. We certainly could easily put a keylogger on a wired keyboard at someone’s desk. There are plenty of vulnerabilities, and in this age of being highly connected through our devices, there are potential issues with all access. There’s no perfect solution, but we should be diligent with physical control of our devices and react swiftly if we think one might be stolen. Remote disable and a good backup might be top of my list.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Daily Coping 14 Apr 2020

    I’ve started to add a daily coping tip to the SQLServerCentral newsletter, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to find a way to get an extra 15 minutes of physical activity, but a fun way.

    I miss volleyball. This is the time of year I’d be winding down the season. This would actually be my last full week of practice before a tournament this weekend and final evals next week. I coach, so I don’t do a lot, but I usually get in a short 15-20 minutes of play with the kids, as well as demonstrating things for them to learn.

    My last practice was Mar 11. The last time I played was Mar 9, when I warmed up one girl before the last match, playing pepper with her.

    My daughter is also stuck here, and she’s missing her spring season at college. I plan to try and get out in the riding arena and practice a bit with her tonight.

    It will be a good release, though I still miss the team.