1. 7
  1.  

  2. 5

    This isn’t always required, but it’s nice to clean it up. If you’re going to make a real application with this, you’ll want to do some additional checking/sanitization on this string. You’ll also want to prepare your query, but that’s outside the scope of this tutorial. For now, we’ll just strip the quotes.

    It’s good that it’s at least mentioned but how many more lines could it be to add a proper prepared statement? We shouldn’t be writing bad example code like 15 years ago, especially if it’s a single route/GET parameter.

    1. 4

      I’ve done this sort of thing, with SQLite, in Go. It wouldn’t even be an extra line, you can just use string concatenation to build the LIKE argument inside of SQLite, so that it’ll be safe from query injection.

      The code goes from this:

      rows, err := db.Query(`
                            select zip, primaryCity, state, county, timezone, latitude, longitude, irsEstimatedPopulation2015 
                            from zip_code_database 
                            where primaryCity like '%" + searchCity + "%' and type = 'STANDARD'`)
      if err != nil {
        log.Fatal(err)
      }
      

      to this:

      rows, err := db.Query(`
                           select zip, primaryCity, state, county, timezone, latitude, longitude, irsEstimatedPopulation2015 
                           from zip_code_database 
                           where primaryCity like '%' || ? || '%' and type = 'STANDARD'`, searchCity)
      if err != nil {
        log.Fatal(err)
      }
      

      A working example of doing this in a working codebase: https://github.com/yumaikas/Gills/blob/master/repository.go#L62-L69

      There’s no reason to not use something like that, other than being unaware of it. (Which, I will admit, can happen).

      1. 1

        Thanks, that’s even fancier than the way I generally do It (just the single question mark). I learned something here.

        1. 1

          Glad I could help! I remember facing this very thing down, and ended up doing some research to see if it’d work. I’m always glad to do what I can to help prevent SQL injections.

          Usually a single question.mark.does the trick for most parameterization needs. This allows you to be more explicit about how to glob the like expression in the SQL query, because the double pipes are concatenating the value in the SQL query. This trick also works in Postgres, and in MSSQL, but with a different syntax. I don’t know of MySQL, but I’m sure it has an equivalent.

      2. 2

        You’re right, not sure what I was thinking. I’ve amended it. This is why I post things to lobsters, I get valuable feedback that helps me improve. Thanks!