smart solution of SQL injection

Question

These is one keyword confliction issue in the query module of my application,please see if you can tell me a smart solution.

First,In query module,each query condition contains three parts in UI:

1.field name,its value is fixed,e.g origin,finalDest...

2.operator,it is a select list which includes "like","not like","in","not in","=","!="

3.value,this part is input by user.then in back-end,it will assemble the SQL statement according to UI's query criteria,e.g if user type/select following stuff in UI

Field Name       Operator       Value
origin           like           CHI
finalDest        in             SEL

In back-end,it will generate following SQL:

select * from Booking where origin like '%CHI%' and finalDest in ('SEL').

But there is a bug,e.g if user type some of special symbol in "value",e.g "'","_" etc,it will lead to the generated SQL also contain ' or _ ,e.g:

select * from Booking where origin like '%C_HI%' and finalDest in ('S'EL').

you could see as there is special symbol in "where" block,the SQL can't be executed

For this problem,my solution is add escape character "/" in front of the special symbol before executing it,but what i know is just ' or _ that would conflict with the SQL keywords,do you know if there is any others similar symbol that i need to handle or do you guys have any better idea that can avoid the injection

Sorry,forgot told you what language i am using,i am using java,the DB is mysql,i also use hibernate,there are a lot of people said why i didn't use PreparedStatement,this is a little complex,simply speaking,in my company,we had a FW called dynamic query,we pre-defined the SQL fragment in a XML file,then we will assemble the SQL according to the UI pass in criteria with the jxel expression,as the SQL is kinda of pre-defined stuff,i afraid if change to use PreparedStatement,it will involve a lot of change for our FW,so what we care is just on how to fix the SQL injection issue with a simple way.

Presumably you are sending this query to a server, the server should have an authentication scheme which only the client may access, and the client should sanitize its database input. — awiebe, Jul 08 '13 at 08:35
HI,i am using mysql5+hibernate,but i assumed it should not depend on mysql and should be a generic cross-platform SQL statement — Chailie, Jul 08 '13 at 09:09

score 2 · Answer 1 · answered Jul 08 '13 at 08:36

2

The code should begin attempting to stop SQL injection on the server side prior to sending any information to the database. I'm not sure what language you are using, but this is normally accomplished by creating a statement that contains bind variables of some sort. In Java, this is a PreparedStatement, other languages contains similar features.

Using bind variables or parameters in a statement will leverage built in protection against SQL injection, which honestly is going to be better than anything you or I write on the database. If your doing any String concatenation on the server side to form a complete SQL statement, this is an indicator of a SQL injection risk.

answered Jul 08 '13 at 08:36

Kevin Bowersox

93,289
19
159
189

Yeah,you are right,but we are where we are,if change to use PreparedStatement,i think this will affect a lot of coding that we supposed it is already stable.you what we need to do is using a smart solution to fix the injection problem,no matter using escape character or whatever,i hope the change won't too many. – Chailie Jul 08 '13 at 09:28
@Chailie If your using hibernate it should already be using PreparedStatements – Kevin Bowersox Jul 08 '13 at 09:30
what we written is like this:em = emf.createEntityManager(); Query query = em.createNativeQuery(sql); ret = query.getResultList();but it will have following exception if i type single quote in UI:17:34:40,927 ERROR JDBCExceptionReporter:101 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' order by BaseBookingInfoTO.bookingDateGMT desc' at line 1 17:34:40,929 ERROR AbstractEntityManagerImpl:580 - Unable to mark for rollback on PersistenceException: java.lang.IllegalStateException: no transaction st... – Chailie Jul 08 '13 at 09:35

score 1 · Accepted Answer · edited May 23 '17 at 12:21

0   An ASCII NUL (0x00) character.
'   A single quote (“'”) character.
"   A double quote (“"”) character.
b   A backspace character.
n   A newline (linefeed) character.
r   A carriage return character.
t   A tab character.
Z   ASCII 26 (Control+Z). See note following the table.
\   A backslash (“\”) character.
%   A “%” character. See note following the table.
_   A “_” character. See note following the table

Reference

Stack Similar Question

score 0 · Answer 3 · answered Jul 08 '13 at 08:44

0

You should use bind variables in your SQL statement. As already mentioned this is done with PreparedStatements in Java.

To make sure, only valid column names are used, you can validate the input against the database. MySQL provides schema information like columns of each table as part of the INFORMATION_SCHEMA. For further information, check the MySQL documentation:

"The INFORMATION_SCHEMA COLUMNS Table"

answered Jul 08 '13 at 08:44

Olaf H

496
2
9

Thanks for your answer,but in my case,the SQL statement is dynamic assembled.so i afraid i can't use PreparedStatement direct – Chailie Jul 08 '13 at 09:12

smart solution of SQL injection

3 Answers3