1

I have an XPath expression in SQL that takes Unicode characters as input. I need to parameterize the query to prevent SQL injection.

When passing the Unicode characters, they are coming as ? instead of the actual characters. So, when passing the string as parameter to the query, the query is not returning any results. The actual string is "abcồn dnưf hiứj" which is a user input from form but when passed as parameter to the query, the Unicode characters are replaced by ? as given below. 

declare @name NVARCHAR(100)=?;
SELECT 
    * 
FROM 
    dbo.EMPLOYEE_DETAILS 
WHERE 
    EMPLOYEE_DETAILS.EMP_XML.value('(/map/name)[1]', 'nvarchar(max)') like @name

When the query is printed in JPA, the parameters are printed like the below.

[params=(String) %abc?n de?f hi?j%]

Adding some more details.

The server used is WebSphere Application Server 8.5 and JPA Provider is Apache OpenJPA.

When the form parameter is printed in the servlet, it did not recognize the unicode characters (abcễn was printed like abc?n). The above nativeQuery did not return any results as there was no string like abc?n in the database. So I added the below entry to web.xml and the following properties to Websphere Application Server.

Custom Properties:

client.encoding.override = UTF-8 file.encoding = UTF-8

Web.xml

<filter>  
<filter-name>encodingFilter</filter-name>  
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>  
<init-param>  
   <param-name>encoding</param-name>  
   <param-value>UTF-8</param-value>  
</init-param>  
<init-param>  
   <param-name>forceEncoding</param-name>  
   <param-value>true</param-value>  
</init-param>  


<filter-mapping>  
<filter-name>encodingFilter</filter-name>  
<url-pattern>/*</url-pattern>

After adding this entry, when I printed the string, they were printed with UTF-8 encoding like abcồ dnưf hiứj (correctly in UTF-8 format) but still the query does not return any results.

When I try the same thing using PreparedStatement, I am getting results. I am facing the issue only when I am using nativeQuery but I have to use only jpa. I suspect my jpa provider does not support unicode characters.

Can someone please help with this? Is there any property to be added to my dataSource to support UTF-8 characters?

user2186831
  • 355
  • 2
  • 10
  • Wrapping value in N'...' might help to preserve the Unicode, however, I have no env to test it right now https://stackoverflow.com/a/10025043/920557 – Eugene Komisarenko Jul 02 '19 at 18:51
  • That syntax (`[params=(String) ...]`) is not valid T-SQL. Where does it come from? What's the client-side technology used to call this statement? I suspect it's something that is not handling Unicode correctly. There should be no need for a `declare` statement if `@name` is passed correctly as a *parameter*, rather than a textually replaced value. – Jeroen Mostert Jul 02 '19 at 18:59
  • @JeroenMostert Apologies for missing that out.I have edited the question now. The params passed is not part of the SQL. It is the one that is the printed in the logs by JPA. – user2186831 Jul 03 '19 at 17:29
  • @EugeneKomisarenko Hi Eugune. Which value are you suggesting to wrap in unicode. If I pass the value with prefix N in parameter, no results are returned as the N prefix is considered a part of the value itself and no such value exists in the database. – user2186831 Jul 03 '19 at 17:32
  • Check out this answer https://stackoverflow.com/a/22920848/920557, perhaps your issue is somewhere near. You may also want to add JPA to the list of tags ;) to bring the attention of the right people.. – Eugene Komisarenko Jul 03 '19 at 17:42
  • @EugeneKomisarenko Thanks. Let me check that link. BTW, I have added the JPA tag now ;) I am using Apache OpenJPA – user2186831 Jul 03 '19 at 17:45
  • @TT Hi TT, any idea on the issue? – user2186831 Jul 03 '19 at 19:06
  • I would not trust what shows up in your log, as the string may have been encoded with the wrong encoding. Can you use a debugger to see what the bytes going to the server look like? – Brian Vosburgh Jul 03 '19 at 23:55
  • @BrianVosburgh The encoding used is UTF-8. It do something support those characters right? – user2186831 Jul 04 '19 at 11:02
  • 1
    There are a number of places where the encoding could be messed up. I'm guessing you are passing a java.lang.String to the OpenJPA query. OpenJPA then encodes this string (into a byte[]) to send it to the server. This may be incorrectly encoded. You should verify how the string is encoded with your debugger. Also, OpenJPA passes the java.lang.String to a logger. That logger then encodes this string (into a byte[]) to write it to a file (or stdout). Again, this encoding may be incorrect and that is why what you see in the log is incorrect, even though the byte[] sent to the server may be OK... – Brian Vosburgh Jul 04 '19 at 15:46
  • The problem is not with SQL Server or your T-SQL, since you are declaring the variable as an NVARCHAR. I suspect the problem occurs before the parameter is passed to the prepared statement. – TT. Jul 04 '19 at 17:20
  • @TT One thing I noticed is that the string is printed with ? even if I simply assign it to a variable and try printing like the below. `String str = "abcễn dnếf hiệj"; System.out.println(str); Output: abc?n de?f hi?j` When I printed the encoding in the servlet, it gave me UTF-8; – user2186831 Jul 04 '19 at 17:36
  • I see that it doesn't work if the value is passed as a parameter in createNativeQuery. So I tried constructing the query dynamically like the below and it works. `declare @name NVARCHAR(100)=N''; SELECT * FROM dbo.EMPLOYEE_DETAILS WHERE EMPLOYEE_DETAILS.EMP_XML.value('(/map/name)[1]', 'nvarchar(max)') like @name` When the query is constructed, I read the value from form and place it in the Could someone please tell if the above still has SQL Injection vulnerability? – user2186831 Jul 04 '19 at 18:18
  • I tried to inject few SQL commands to the above statement (parameter) but they failed since I am enclosing inside single quotes '' (the command is considered part of the value) and not returning any results. But I am not sure if it is not vulnerable to SQL injection anymore. Would be great if someone could confirm. – user2186831 Jul 04 '19 at 18:23
  • It has an SQL injection vulnerability problem, if you don't double the single quotes. In Java, do a `stringVar.replace("'","''")` before inserting the string. – TT. Jul 04 '19 at 20:09
  • @TT : How is it vulnerable. Can you please give me an example. – user2186831 Jul 04 '19 at 20:16
  • There are many ways that SQL Injection can be leveraged. One would with name as: `TT';DROP TABLE dbo.EMPLOYEE_DETAILS;--`. Dropping the table won't work if you have foreign keys that reference to that table. But if you let exception/error details percolate through to the client (be it browser based or not), a hacker might eventually get to know the database structure and craft the SQL commands to wipe your database. Or select information you don't want them to have. – TT. Jul 05 '19 at 02:18
  • Doubling the single quotes in the name in my comment, will stop the nasty drop command from executing. – TT. Jul 05 '19 at 02:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/196025/discussion-between-user2186831-and-tt). – user2186831 Jul 05 '19 at 05:56
  • @Brian Vosburgh Hi Brian, How to set the encoding to UTF-8 in datasource? The server used is Websphere Application Server 8. – user2186831 Jul 08 '19 at 18:57

0 Answers0