0

I need to use some queries like

select * from StudentTable where ${columnName}=#{columnValue}

Note that columnName and columnValue are provided by user. I'm using spring boot application with Mybatis. This query works as intended but it reports the possibility of SQL Injection in some tools. How can I make this work in a different way?

select * from StudentTable where #{columnName}=#{columnValue}

This will not work, no output.

Select * from StudentTable where Joiningdate < NOW()-INTERVAL '${configureddays} DAY'

This one also reports the possibility of SQL Injection but the query works. Replacing with # will not help here too. How can I restructure both these queries?

RisingDev
  • 1
  • 2
  • Regarding 'why?', see this [answer](https://stackoverflow.com/a/1582192/1261766) and this [FAQ entry](https://github.com/mybatis/mybatis-3/wiki/FAQ#what-is-the-difference-between--and-). You need to use `${}` and it's not that hard to avoid SQL injection. Usually, the developer (=you) knows the target columns, so a simple check like `if (COLUMN_LIST.contains(columnName))` before executing the statement is good enough. If you don't know the target columns beforehand, you may have to retrieve the column names from `INFORMATION_SCHEMA` or alike (it depends on the DB you use). – ave Apr 22 '21 at 05:47
  • Thankyou for the response. I'm new to this. If i have 10 columns and I'm allowing the user to query the database using values of any of these columns, i will have to add 10 'if' conditions right? I want to avoid that, hence I'm using a Map with key value pair. Also this query is being used by different apis to fetch results according to different conditions. Using information_schema and bringing out all column names exposed, is that method recommended? Correct me if I've understood it wrong – RisingDev May 02 '21 at 06:15
  • You don't need 10 `if`s if I understand correctly. Assuming `allowedColumns` is a list of column names users can choose, it is safe if `allowedColumns.containsAll(map.keySet())` returns `true`. The rule is simple: the column name that user specified must match the actual column name. Regarding the necessity of using `information_schema`, it entirely depends on your requirement. For example, if your API allows users to query any columns in the DB and the schema changes over time (i.e. columns can be added/deleted), there aren't many options, right? – ave May 02 '21 at 10:11

0 Answers0