Distributed Relational Database Service (DRDS) provides support for server-side prepared statements. This support uses the efficient client/server binary protocol. Using prepared statements with placeholders for parameter values has the following benefits:
- Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to variable values in clauses of prepared statements.
- Protection against SQL injection attacks.
- Supported syntax
- Supported SQL statements
- All data manipulation language (DML) statements are allowed in DRDS prepared statements, including SELECT, UPDATE, DELETE, and INSERT.
- Non-supported SQL statements:
- Other SQL statements than DML statements are not allowed in DRDS prepared statements, for example, SHOW and SET.
- Prepared statements cannot be used in the MySQL command lines.
- For example, the following statements are not supported:mysql> SET @s = ‘SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse’;mysql> PREPARE stmt2 FROM @s;mysql> SET @a = 6;mysql> SET @b = 8;mysql> EXECUTE stmt2 USING @a, @b;
- To use prepared statements on a Java client, you must forcibly add the useServerPrepStmts=true parameter to the URL string. If you do not specify this parameter, PreparedStatement will perform a normal query by default.
- For example, jdbc:mysql:// xxxxxx:3306/xxxxxx? useServerPrepStmts=true
Connection connection = DriverManager.getConnection("jdbc:mysql://xxxxxx:3306/xxxxxx? useServerPrepStmts=true", "xxxxx", "xxxxx");
String sql = "insert into batch values(?,?)" ;
PreparedStatement preparedStatement = connection.prepareStatement(sql);