3

There is a legacy application that uses a table to translate job names to filenames. This legacy application queries it as follows:

SELECT filename FROM aJobTable WHERE jobname = 'myJobName'

But in reality those jobnames always match the filenames (e.g. 'myJobName.job' is the jobname but also the filename) That makes this table appear unnecessary. But unfortunately, we cannot change the code of this program, and the program just needs to select it from a table.

That's actually a bit annoying. Because we do need to keep this database in sync. If a jobname is not in the table, then it cannot be used. So, as our only way out, right now we have some vbscripts to synchronize this table, adding records for each possible filename. As a result, the table just 2 columns with identical values. -- We want to get rid of this.

So, we have been dreaming about some hack that queries the data with the jobname, but just always returns the jobname again, like a copy/mirror query. Then we don't actually have to populate a table at all.

"Exploits"

The following can be configured in this legacy application. My hunch is that these may open the door for some tricks/hacks.

  • use of either MS Access or SQL Server (we prefer sql server)
  • The name of the table (e.g. aJobTable)
  • The name of the filename column (e.g. filename)
  • The name of the jobname column (e.g. jobname)

Here is what I came up with:

If I create a table-valued function mirror(a) then I get pretty close to what I want. Then I could use it like

SELECT filename FROM mirror('MyJobName.job')

But that's just not good enough, it would be if I could force it to be like

SELECT filename FROM mirror WHERE param1 = 'MyJobName.job'

Unfortunately, I don't think it's possible to call functions like that.

So, I was wondering if perhaps somebody else knows how to get it working.

So my question is: "How can you create a table (or other object) that always returns the value passed to its WHERE-clause, like a mirror."

Community
  • 1
  • 1
bvdb
  • 22,839
  • 10
  • 110
  • 123
  • I don't think you can read/get the `WHERE` clause value in any object. I understand the issue, but sometimes (and I am doing this over and over again) the best thing to do is to pay the time for rewriting the legacy code. – gotqn Nov 13 '18 at 09:37
  • can please try like SELECT @jobname as Filename – Sanal Sunny Nov 13 '18 at 09:48
  • So the application creates the SQL query and sends it to the relevant database as a string, is that correct? – Zohar Peled Nov 13 '18 at 09:55
  • Are you saying that app always concocts a string of form `select A from B where C = 'D'`. If it is just concatenating it and not parameterising it the you can try SQL injection – Nick.Mc Nov 13 '18 at 11:23
  • For example `select Dummyfield1 from EmptyTable where Dummyfield2 = '' UNION ALL SELECT 'The Real Value';` – Nick.Mc Nov 13 '18 at 11:25
  • @Zohar Peled that's correct – bvdb Nov 13 '18 at 12:38
  • @Nick.McDermaid that's a good idea ! - I'll try that – bvdb Nov 13 '18 at 12:39
  • Of course if it is susceptible to sql injection, that’s another reason to replace it. – Nick.Mc Nov 13 '18 at 13:16
  • @Nick.McDermaid It's is an application written in the '90s, that interfaces with a dozen of old industrial printers (the kind that prints best-before dates on consumer goods). It runs on an isolated network, not accessible to the internet. The source code is not available. It does not have a web interface. And its configuration tool is secured with a password. And there's at least a dozen customers of ours that have similar setups. It's hard to eliminate/replace them, because it would be expensive, and often have hidden customizations.- Eliminating this access database would be a big step. ;-) – bvdb Nov 13 '18 at 13:41
  • Why not define the column `filename` as a (virtual) computed column that returns the value of `jobname`? –  Nov 13 '18 at 15:13

1 Answers1

1

It's kinda hard to answer not knowing the code that the application use, but if we assume it only takes strings and concatenate them without any tests whatsoever, I would assume code like this: (translated to c#)

var sql = "SELECT "+ field +" FROM "+ table +" WHERE "+ conditionColumn +" = '"+ searchValue +"'";

As this is an open door for SQL injection, and given the fact that SQL Server allows you two ways of creating an alias - value as alias and alias = value, you can take advantage of that and try to generate an SQL statement like this:

SELECT field /* FROM table WHERE conditionColumn */ = 'searchValue'

So field should be "field /* ",
and conditionColumn should be "conditionColumn */"

table name doesn't matter, you could leave an empty string for it.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Looks like the best idea so far. – bvdb Nov 13 '18 at 16:09
  • It does depend in a few assumptions, but knowing how we used to write code back then, it might just as well be the case and therefor the simplest solution. – Zohar Peled Nov 13 '18 at 16:11
  • 1
    Come to think about it, `conditionColumn` might just as well be `"*/"`.... – Zohar Peled Nov 13 '18 at 16:16
  • I'm curious to know if this works. Will you please test it and leave a comment? – Zohar Peled Nov 14 '18 at 12:14
  • It didn't work, but it was close. I think the reason, is that the resulting sql statement is probably something like `SELECT * FROM table WHERE */ = 'searchValue'`, and then the fieldname is used to extract data from the resultset. Oh well, it was a good attempt. I think this is as close as I will ever get. - Good job. - At least, I learned a couple of things about SQL injection, thanks. – bvdb Nov 14 '18 at 13:07
  • can you run a profiler on SQL Server to get the query that actually gets executed? because if you can, it might help you decide if you actually can use sql injection tricks to solve this. – Zohar Peled Nov 14 '18 at 13:09