Java - escape string to prevent SQL injection

Question

I m trying to put some anti sql injection in place in java and am finding it very difficult to work with the the  replaceAll  string function  Ultimately I need a function that will convert any existing   to     any   to     any   to     and any  n to   n so that when the string is evaluated by MySQL SQL injections will be blocked    I ve jacked up some code I was working with and all the             in the function are making my eyes go nuts  If anyone happens to have an example of this I would greatly appreciate it

User · Answer

You need the following code below  At a glance  this may look like any old code that I made up  However  what I did was look at the source code for http   grepcode com file repo1 maven org maven2 mysql mysql-connector-java 5 1 31 com mysql jdbc PreparedStatement java  Then after that  I carefully looked through the code of setString int parameterIndex  String x  to find the characters which it escapes and customised this to my own class so that it can be used for the purposes that you need  After all  if this is the list of characters that Oracle escapes  then knowing this is really comforting security-wise  Maybe Oracle need a nudge to add a method similar to this one for the next major Java release   public class SQLInjectionEscaper        public static String escapeString String x  boolean escapeDoubleQuotes            StringBuilder sBuilder   new StringBuilder x length     11 10            int stringLength   x length             for  int i   0  i  lt  stringLength    i                char c   x charAt i                switch  c                case 0     Must be escaped for  mysql                     sBuilder append                        sBuilder append  0                     break               case   n      Must be escaped for logs                    sBuilder append                        sBuilder append  n                     break               case   r                   sBuilder append                        sBuilder append  r                     break               case                       sBuilder append                        sBuilder append                         break               case                       sBuilder append                        sBuilder append                         break               case         Better safe than sorry                    if  escapeDoubleQuotes                        sBuilder append                                           sBuilder append                        break               case   032      This gives problems on Win32                    sBuilder append                        sBuilder append  Z                     break               case   u00a5               case   u20a9                      escape characters interpreted as backslash by mysql                    fall through              default                  sBuilder append c                                    return sBuilder toString

User · Answer

In case you are dealing with a legacy system  or you have too many places to switch to PreparedStatements in too little time - i e  if there is an obstacle to using the best practice suggested by other answers  you can try AntiSQLFilter

User · Answer

Prepared Statements are the best solution  but if you really need to do it manually you could also use the StringEscapeUtils class from the Apache Commons-Lang library  It has an escapeSql String  method  which you can use   import org apache commons lang StringEscapeUtils      String escapedSQL   StringEscapeUtils escapeSql unescapedSQL

User · Answer

From Source public String MysqlRealScapeString String str     String data   null    if  str    null  amp  amp  str length    gt  0        str   str replace  quot    quot    quot      quot        str   str replace  quot   quot    quot     quot        str   str replace  quot  0 quot    quot   0 quot        str   str replace  quot  n quot    quot   n quot        str   str replace  quot  r quot    quot   r quot        str   str replace  quot   quot  quot    quot     quot  quot        str   str replace  quot   x1a quot    quot   Z quot        data   str        return data

User · Answer

If really you can t use Defense Option 1  Prepared Statements  Parameterized Queries  or Defense Option 2  Stored Procedures  don t build your own tool  use the OWASP Enterprise Security API  From the OWASP ESAPI hosted on Google Code      Don   t write your own security controls  Reinventing the wheel when it comes to developing security controls for every web application or web service leads to wasted time and massive security holes  The OWASP Enterprise Security API  ESAPI  Toolkits help software developers guard against security-related design and implementation flaws    For more details  see Preventing SQL Injection in Java and SQL Injection Prevention Cheat Sheet    Pay a special attention to Defense Option 3  Escaping All User Supplied Input that introduces the OWASP ESAPI project

User · Answer

PreparedStatements are the way to go  because they make SQL injection impossible   Here s a simple example taking the user s input as the parameters   public insertUser String name  String email       Connection conn   null     PreparedStatement stmt   null     try         conn   setupTheDatabaseConnectionSomehow          stmt   conn prepareStatement  INSERT INTO person  name  email  values                 stmt setString 1  name         stmt setString 2  email         stmt executeUpdate            finally         try            if  stmt    null    stmt close                    catch  Exception e                log this error               try            if  conn    null    conn close                    catch  Exception e                log this error                  No matter what characters are in name and email  those characters will be placed directly in the database   They won t affect the INSERT statement in any way   There are different set methods for different data types -- which one you use depends on what your database fields are   For example  if you have an INTEGER column in the database  you should use a setInt method   The PreparedStatement documentation lists all the different methods available for setting and getting data

User · Answer

The only way to prevent SQL injection is with parameterized SQL  It simply isn t possible to build a filter that s smarter than the people who hack SQL for a living   So use parameters for all input  updates  and where clauses  Dynamic SQL is simply an open door for hackers  and that includes dynamic SQL in stored procedures  Parameterize  parameterize  parameterize

User · Answer

This is in answer to the OP s comment under the original question  I agree completely that PreparedStatement is the tool for this job  not regexes    When you say  n  do you mean the sequence   n or an actual linefeed character   If it s   n  the task is pretty straightforward   s   s replaceAll                    0      To match one backslash in the input  you put four of them in the regex string  To put one backslash in the output  you put four of them in the replacement string   This is assuming you re creating the regexes and replacements in the form of Java String literals   If you create them any other way  e g   by reading them from a file   you don t have to do all that double-escaping   If you have a linefeed character in the input and you want to replace it with an escape sequence  you can make a second pass over the input with this   s   s replaceAll   n        n      Or maybe you want two backslashes  I m not too clear on that    s   s replaceAll   n            n

User · Answer

Using a regular expression to remove text which could cause a SQL injection sounds like the SQL statement is being sent to the database via a Statement rather than a PreparedStatement   One of the easiest ways to prevent an SQL injection in the first place is to use a PreparedStatement  which accepts data to substitute into a SQL statement using placeholders  which does not rely on string concatenations to create an SQL statement to send to the database   For more information  Using Prepared Statements from The Java Tutorials would be a good place to start

User · Answer

After searching an testing alot of solution for prevent sqlmap from sql injection  in case of legacy system which cant apply prepared statments every where   java-security-cross-site-scripting-xss-and-sql-injection topic WAS THE SOLUTION  i tried  Richard s solution but did not work in my case  i used a filter     The goal of this filter is to wrapper the request into an own-coded   wrapper MyHttpRequestWrapper which transforms       the HTTP parameters with special characters   lt                into HTML   codes via the org springframework web util HtmlUtils htmlEscape        method  Note  There is similar classe in Apache Commons     org apache commons lang StringEscapeUtils escapeHtml      the SQL   injection characters                 via the Apache Commons classe   org apache commons lang StringEscapeUtils escapeSql         lt filter gt   lt filter-name gt RequestWrappingFilter lt  filter-name gt   lt filter-class gt com huo filter RequestWrappingFilter lt  filter-class gt   lt  filter gt    lt filter-mapping gt   lt filter-name gt RequestWrappingFilter lt  filter-name gt   lt url-pattern gt    lt  url-pattern gt   lt  filter-mapping gt      package com huo filter   import java io IOException   import javax servlet Filter  import javax servlet FilterChain  import javax servlet FilterConfig  import javax servlet ServletException  import javax servlet ServletRequest  import javax servlet ServletReponse  import javax servlet http HttpServletRequest   public class RequestWrappingFilter implements Filter       public void doFilter ServletRequest req  ServletReponse res  FilterChain chain  throws IOException  ServletException          chain doFilter new MyHttpRequestWrapper req   res              public void init FilterConfig config  throws ServletException             public void destroy   throws ServletException              package com huo filter   import java util HashMap  import java util Map   import javax servlet ServletException  import javax servlet http HttpServletRequest  import javax servlet http HttpServletRequestWrapper   import org apache commons lang StringEscapeUtils   public class MyHttpRequestWrapper extends HttpServletRequestWrapper      private Map lt String  String   gt  escapedParametersValuesMap   new HashMap lt String  String   gt          public MyHttpRequestWrapper HttpServletRequest req           super req               Override     public String getParameter String name           String   escapedParameterValues   escapedParametersValuesMap get name           String escapedParameterValue   null           if escapedParameterValues  null               escapedParameterValue   escapedParameterValues 0            else              String parameterValue   super getParameter name                   HTML transformation characters             escapedParameterValue   org springframework web util HtmlUtils htmlEscape parameterValue                   SQL injection characters             escapedParameterValue   StringEscapeUtils escapeSql escapedParameterValue                escapedParametersValuesMap put name  new String   escapedParameterValue               end-else          return escapedParameterValue              Override     public String   getParameterValues String name           String   escapedParameterValues   escapedParametersValuesMap get name           if escapedParameterValues  null               String   parametersValues   super getParameterValues name               escapedParameterValue   new String parametersValues length                                for int i 0  i lt parametersValues length  i                     String parameterValue   parametersValues i                   String escapedParameterValue   parameterValue                      HTML transformation characters                 escapedParameterValue   org springframework web util HtmlUtils htmlEscape parameterValue                       SQL injection characters                 escapedParameterValue   StringEscapeUtils escapeSql escapedParameterValue                    escapedParameterValues i    escapedParameterValue                 end-for              escapedParametersValuesMap put name  escapedParameterValues              end-else          return escapedParameterValues

User · Answer

If you are using PL SQL you can also use DBMS ASSERT it can sanitize your input so you can use it without worrying about SQL injections   see this answer for instance  https   stackoverflow com a 21406499 1726419

User · Answer

PreparedStatements are the way to go in most  but not all cases  Sometimes you will find yourself in a situation where a query  or a part of it  has to be built and stored as a string for later use  Check out the SQL Injection Prevention Cheat Sheet on the OWASP Site for more details and APIs in different programming languages

[java] Java - escape string to prevent SQL injection

Examples related to java

Examples related to sql

Examples related to regex

Examples related to escaping

Examples related to sql-injection