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?