Geek Answers Handbook

Does the prepared statement provide an SQL-Injection warning here

Below is the code from the SAMATE Reference Dataset . I used it to test a static analysis tool. As you can see, the code should prevent SQL injection using both the sanitization method and the prepared statement.

Since SCA tools cannot know custom santing methods, it has not been discovered that the allowed method is used to prevent injection.

 public class SQLInjection_good_089 extends HttpServlet { private static final long serialVersionUID = 1L; public SQLInjection_good_089() { super(); } // Table of allowed names to use final String allowed_names[] = { "Mickael", "Mary", "Peter", "Laura", "John"}; // Function to check if the current name takes part of the allowed ones public boolean allowed( String in ) { boolean bool = false; for( int i = 0; i < 5; i++ ) { if( in.equals( allowed_names[i] ) ) { // the current name is allowed to use bool = true; break; } } return bool; } // Method which will be called to handle HTTP GET requests protected void doGet( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException { // Initialize the output stream resp.setContentType("text/html"); ServletOutputStream out = resp.getOutputStream(); out.println("<HTML><BODY><blockquote><pre>"); Connection conn = null; // Get the parameter "name" from the data provided by the user String name = req.getParameter( "name" ); if ( (name != null) && (allowed(name) == true) ) { try { // Set the context factory to use to create the initial context System.setProperty (Context.INITIAL_CONTEXT_FACTORY, "your.ContextFactory"); // Create the initial context and use it to lookup the data source InitialContext ic = new InitialContext (); DataSource dataSrc = (DataSource) ic.lookup ("java:comp/env/jdbc:/mydb"); // Create a connection to the SQL database from the data source conn = dataSrc.getConnection(); // Send a SQL request to the database PreparedStatement ps = conn.prepareStatement( "SELECT * FROM users WHERE firstname LIKE ?" ); // replace the first parameter by name ps.setString(1, name); ps.executeQuery(); } catch( NamingException e ) { out.println( "Naming exception"); } catch( SQLException e ) { out.println( "SQL exception"); } finally { try { if (conn != null) conn.close (); } catch (SQLException se) { out.println("SQL Exception"); } } } else return; out.println( "</pre></blockquote></body></html>" ); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } }

However, I think that using a prepared statement should in any case prevent an injection. Am I mistaken?

+7
java security sql-injection prepared-statement static-analysis
source share
4 answers

You're right. prepared statement will take care of the "illegal" input of SQL.

The allowed(...) function is a kind of verification of user input in terms of business rules and not to prevent SQL injection.

+3
source share

Yes The prepared statement here will prevent sql injection. This is because you are using a placeholder (?) In the query. It is important to note the placeholder here.

The following are two examples of prepared statements. The first will not impede sql implementation.

PreparedStatement ps = conn.prepareStatement ("SELECT * FROM users WHERE firstname LIKE" + name);

The above statement, even if prepared, will not impede sql implementation

However, the prepared statement below is good for preventing sql injection.

PreparedStatement ps = conn.prepareStatement ("SELECT * FROM users WHERE firstname LIKE?");

The difference b / w of the first and second operators is that, although the request in the first case is dynamically compiled at run time, in the second case it is precompiled.

This means that malicious user input of the type (a'or'1 '=' 1) can modify the request in the first expression. But the second query, since it is precompiled, will process malicious user input as data, not the sql command.

In a nutshell, Assumed Statements prevent SQL injection if and only if they are used with placeholders and Bind parameters.

+3
source share

Just a prepared statement should be sufficient to prevent SQL injection ...

However, if you intend to write custom messages in the "out" parameter (for example, out.printf( "Invalid username %s", name ) ), watch out for javascript insertion. I could enter my name as <script>alert('hi')</script>

+1
source share

This seems to prevent SQL injection. You are of course right that the allowed () function helps, but is not the preferred method. Since your code is just a sample, I would suggest that in the real world most programs will allow you to use more than 5 possible options.

+1
source share

More articles:


All Articles