How does Java's PreparedStatement work?

Question

I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.

Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the ? in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the ?s in the queries instead of actual values? I am quite confounded.

Are you asking if you can use a String-valued function in place of a String literal? Are you asking if you can use an int-valued function in place of a literal integer? Can you provide a code snippet? — S.Lott, Jan 07 '09 at 02:54

score 14 · Answer 1 · edited Feb 08 '17 at 14:07

14

If you have a variable that comes from user input, it's essential that you use the ? rather than concatenating the strings. Users might enter a string maliciously, and if you drop the string straight into SQL it can run a command you didn't intend.

I realise this one is overused, but it says it perfectly:

Little Bobby Tables

edited Feb 08 '17 at 14:07

Community

1
1

answered Jan 07 '09 at 03:25

Marcus Downing

10,054
10
63
85

2

I believe, you mean SQL Injections. – Adeel Ansari Jan 07 '09 at 04:20

score 11 · Accepted Answer · answered Jan 07 '09 at 02:58

11

If you have variables use the '?'

int temp = 75;
PreparedStatement pstmt = con.prepareStatement(
    "UPDATE test SET num = ?, due = now() ");
pstmt.setInt(1, temp); 
pstmt.executeUpdate():

Produces an sql statment that looks like:

UPDATE test SET num = 75, due = now();

answered Jan 07 '09 at 02:58

Mark

16,772
9
42
55

This is not correct for most database systems. Most database systems will prepare the query text, and then send the parameter values on execute. This will never produce a statement that literally contains the parameter value. – Mark Rotteveel Jul 18 '20 at 07:16

score 0 · Answer 3 · answered Jan 08 '09 at 15:47

You don't have to use placeholders in a PreparedStatement. Something like:

PreparedStatement stmt = con.prepareStatement("select sysdate from dual");

would work just fine. However, you can't use a placeholder and then bind a function call to it. Something like this can't be used to call the sysdate function:

PreparedStatement stmt = con.prepareStatement("select ? from dual");
stmt.setSomethingOrOther(1, "sysdate");

score 0 · Answer 4 · answered Jan 11 '09 at 02:45

If you are calling built in functions of your SQL server then use PreparedStatement.

If you are calling stored procedures that have been loaded onto your SQL server then use CallableStatement.

Use question marks as placeholders for function/procedure parameters that you are passing and function return values you are receiving.

score -1 · Answer 5 · answered Dec 10 '13 at 08:27

I've developed a function that allows you to use named parameters in your SQL queries:

private PreparedStatement generatePreparedStatement(String query, Map<String, Object> parameters) throws DatabaseException
    {
        String paramKey = "";
        Object paramValue = null;
        PreparedStatement statement = null;
        Pattern paramRegex = null; 
        Matcher paramMatcher = null;
        int paramIndex = 1;

        try
        {
            //Create the condition
            paramRegex = Pattern.compile("(:[\\d\\w_-]+)", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
            paramMatcher = paramRegex.matcher(query);
            statement = this.m_Connection.prepareStatement(paramMatcher.replaceAll("?"),
                                ResultSet.TYPE_FORWARD_ONLY,
                                ResultSet.CONCUR_READ_ONLY, 
                                ResultSet.HOLD_CURSORS_OVER_COMMIT);

            //Check if there are parameters
            paramMatcher = paramRegex.matcher(query);
            while (paramMatcher.find()) 
            {
                paramKey = paramMatcher.group().substring(1);
                if(parameters != null && parameters.containsKey(paramKey))
                {
                    //Add the parameter 
                    paramValue = parameters.get(paramKey);
                    if (paramValue instanceof Date) 
                    {
                        statement.setDate(paramIndex, (java.sql.Date)paramValue);                 
                    } 
                    else if (paramValue instanceof Double) 
                    {
                        statement.setDouble(paramIndex, (Double)paramValue);                  
                    } 
                    else if (paramValue instanceof Long) 
                    {
                        statement.setLong(paramIndex, (Long)paramValue);                  
                    } 
                    else if (paramValue instanceof Integer) 
                    {
                        statement.setInt(paramIndex, (Integer)paramValue);                
                    } 
                    else if (paramValue instanceof Boolean) 
                    {
                        statement.setBoolean(paramIndex, (Boolean)paramValue);                
                    } 
                    else 
                    {
                        statement.setString(paramIndex, paramValue.toString());     
                    }
                }
                else
                {
                    throw new DatabaseException("The parameter '" + paramKey + "' doesn't exists in the filter '" + query + "'");
                }

                paramIndex++;
            }
        }
        catch (SQLException  l_ex) 
        {
            throw new DatabaseException(tag.lib.common.ExceptionUtils.getFullMessage(l_ex));
        }

        return statement;
    }

You can use it this way:

Map<String, Object> pars = new HashMap<>();
pars.put("name", "O'Really");
String sql = "SELECT * FROM TABLE WHERE NAME = :name";

How does Java's PreparedStatement work?

5 Answers5

Linked

Related