CODEWITHSUNDEEP
mysqlsqldatabase
Chailie
Chailie

Reputation: 451

smart solution of SQL injection

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.

Upvotes: 0

Views: 238

Answers (3)

Olaf H
Olaf H

Reputation: 496

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"

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 1

Kevin Bowersox
Kevin Bowersox

Reputation: 94429

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.

Upvotes: 2

Related Questions