Test if sql with parameters is valid

@param con database connection
@param sql sql
@return return message error if sql is not valid, null otherwise
432448
public static String isValidSqlWithMessage(Connection con, String sql, List<QueryParameter> parameters) {		
		try {
			QueryUtil qu = new QueryUtil(con, DialectUtil.getDialect(con));
			Map<String, QueryParameter> params = new HashMap<String, QueryParameter>();			
			for (QueryParameter qp : parameters) {
				params.put(qp.getName(), qp);
			}
			qu.getColumnNames(sql, params);
		} catch (Exception ex) {
			LOG.error(ex.getMessage(), ex);
			return ex.getMessage();
		}
		return null;
	}
127503
public static String checkCompatibilityIssues( String sql ) {
        sql = sql.replaceAll("LONG PRIMARY KEY AUTOINCREMENT", "INTEGER PRIMARY KEY AUTOINCREMENT");
        sql = sql.replaceAll("AUTO_INCREMENT", "AUTOINCREMENT");
        return sql;
    }
354620
public static <T> T getValueOfCertainColumnOfCertainRecord(String sql, Object... arg) {
        List<T> list = DBUtils.getValueOfCertainColumnOfAllRecord(sql, arg);
        if (list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }
590822
def _condition_as_sql(self, qn, connection):
        '''
        
        '''
        def escape(value):
            if isinstance(value, bool):
                value = str(int(value))
            if isinstance(value, six.string_types):
                # Escape params used with LIKE
                if '%' in value:
                    value = value.replace('%', '%%')
                # Escape single quotes
                if "'" in value:
                    value = value.replace("'", "''")
                # Add single quote to text values
                value = "'" + value + "'"
            return value

        sql, param = self.condition.query.where.as_sql(qn, connection)
        param = map(escape, param)

        return sql % tuple(param)
252708
public Long execute(final ConnectionResource _con)
        throws SQLException
    {
        final boolean supGenKey = Context.getDbType().supportsGetGeneratedKeys();

        Long ret = null;
        if (this.newId && !supGenKey)  {
            ret = Context.getDbType().getNewId(_con, getTableName(), getIdColumn());
            this.column(getIdColumn(), ret);
        }

        final StringBuilder cmd = new StringBuilder()
            .append(Context.getDbType().getSQLPart(SQLPart.INSERT))
            .append(" ")
            .append(Context.getDbType().getSQLPart(SQLPart.INTO))
            .append(" ")
            .append(Context.getDbType().getTableQuote())
            .append(getTableName())
            .append(Context.getDbType().getTableQuote())
            .append(" ")
            .append(Context.getDbType().getSQLPart(SQLPart.PARENTHESIS_OPEN));

        final StringBuilder val = new StringBuilder();

        // append SQL values
        boolean first = true;
        for (final ColumnWithSQLValue col : getColumnWithSQLValues())  {
            if (first)  {
                first = false;
            } else  {
                cmd.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
                val.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
            }
            cmd.append(Context.getDbType().getColumnQuote())
                .append(col.getColumnName())
                .append(Context.getDbType().getColumnQuote());
            val.append(col.getSqlValue());
        }

        // append values
        for (final AbstractColumnWithValue<?> col : getColumnWithValues())  {
            if (first)  {
                first = false;
            } else  {
                cmd.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
                val.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
            }
            cmd.append(Context.getDbType().getColumnQuote())
                .append(col.getColumnName())
                .append(Context.getDbType().getColumnQuote());
            val.append('?');
        }
        cmd.append(Context.getDbType().getSQLPart(SQLPart.PARENTHESIS_CLOSE))
            .append(Context.getDbType().getSQLPart(SQLPart.VALUES))
            .append(Context.getDbType().getSQLPart(SQLPart.PARENTHESIS_OPEN))
            .append(val)
            .append(Context.getDbType().getSQLPart(SQLPart.PARENTHESIS_CLOSE));

        SQLInsert.LOG.debug("Executing SQL: {}", cmd.toString());

        final PreparedStatement stmt;
        if (this.newId && supGenKey) {
            if (Context.getDbType().supportsMultiGeneratedKeys()) {
                stmt = _con.prepareStatement(cmd.toString(), new String[]{ getIdColumn() });
            } else {
                stmt = _con.prepareStatement(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
            }
        } else {
            stmt = _con.prepareStatement(cmd.toString());
        }

        int index = 1;
        for (final AbstractColumnWithValue<?> col : getColumnWithValues())  {
            if (SQLInsert.LOG.isDebugEnabled()) {
                SQLInsert.LOG.debug("    " + index + " = " + col.getValue());
            }
            col.set(index++, stmt);
        }

        try {
            final int rows = stmt.executeUpdate();
            if (rows == 0) {
                throw new SQLException("Object for SQL table '" + getTableName()
                        + "' does not exists and was not inserted.");
            }

            // if auto generated get new id
            if (this.newId && supGenKey) {
                final ResultSet resultset = stmt.getGeneratedKeys();
                if (resultset.next()) {
                    ret = resultset.getLong(1);
                }
                resultset.close();
            }
        } finally  {
            stmt.close();
        }

        if (this.newId && SQLInsert.LOG.isDebugEnabled()) {
            SQLInsert.LOG.debug("new generated id " + ret);
        }

        return ret;
    }
1172090
public static boolean restrictQueryExecution(String sql) {
		String[] restrictions = { "delete", "truncate", "update", "drop", "alter" };
		if (sql != null) {
			sql = sql.toLowerCase();
			for (String restriction : restrictions) {
				if (sql.startsWith(restriction)) {
					return true;
				}
				String regex = "\\s+" + restriction + "\\s+";
				Pattern pattern = Pattern.compile(regex);
				Matcher matcher = pattern.matcher(sql);
				if (matcher.find()) {
					return true;
				}
			}
		}
		return false;
	}
618424
private void validateQuerySql(SQLiteDatabase db, String sql,
                                  CancellationSignal cancellationSignal) {
        db.getThreadSession().prepare(sql,
                db.getThreadDefaultConnectionFlags(true /*readOnly*/), cancellationSignal, null);
    }
874960
@Override
    public SqlQuery createSqlQuery(String sql) {
        Assert.hasText(sql, "sql must has text");
        return ebeanServer.createSqlQuery(sql);
    }
1117152
public static void close(@Nullable Connection con) throws SQLException {
		if (con != null && !con.isClosed()) {
			if (!con.getAutoCommit()) {
				con.rollback();
				con.setAutoCommit(true);
			}
			con.close();
		}
	}
710626
private String validateDialect(String confParam)
   {
      for (String dbType : DBConstants.DB_DIALECTS)
      {
         if (confParam.equals(dbType))
         {
            return dbType;
         }
      }

      return DBConstants.DB_DIALECT_AUTO; // by default
   }
1018708
private boolean isSuccessful(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        try {
            statement.execute(substitute(sql));
        } catch (SQLException e) {
            return false;
        } finally {
            DbUtils.closeQuietly(statement);
        }
        return true;
    }
1142443
private boolean isSchemaValid() throws SQLException {
		logger.entering(CLASSNAME, "isSchemaValid");
		Connection conn = getConnectionToDefaultSchema();
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getSchemas();
		while(rs.next()) {
			if (schema.equalsIgnoreCase(rs.getString("TABLE_SCHEM")) ) {
				cleanupConnection(conn, rs, null);
				logger.exiting(CLASSNAME, "isSchemaValid", true);
				return true;
			}
		}
		cleanupConnection(conn, rs, null);
		logger.exiting(CLASSNAME, "isSchemaValid", false);
		return false;
	}
863138
public boolean validate() {
        int validationTimeout = dsConfig.get().validationTimeout;

        final boolean isTraceOn = TraceComponent.isAnyTracingEnabled();
        if (isTraceOn && tc.isEntryEnabled()) 
            Tr.entry(this, tc, "validate", "timeout: " + validationTimeout);

        try {
            if (validationTimeout >= 0 && mcf.jdbcDriverSpecVersion >= 40) // validation by timeout
            {
                // Validation by timeout is available only on JDBC 4.0 & higher
                if (!sqlConn.isValid(validationTimeout)) 
                {
                    if (isTraceOn && tc.isEntryEnabled())
                        Tr.exit(this, tc, "validate", false);
                    return false;
                }
            } else // validation by SQL query for JDBC drivers at spec level prior to v4.0
            {
                Statement stmt = sqlConn.createStatement();
                try {
                    stmt.executeQuery("SELECT 1").close(); // if this fails, the SQLException is checked for stale
                } finally {
                    stmt.close();
                }
            }

            //  - if currentAutoCommit is false, rollback the connection.
            if (!currentAutoCommit) {
                if (isTraceOn && tc.isDebugEnabled()) 
                    Tr.debug(this, tc, "AutoCommit is false.  Rolling back the connection after validation");
                sqlConn.rollback();
            }

            // Clean up the connection.
            mcf.helper.doConnectionCleanup(sqlConn);

            // Clear the warning.
            sqlConn.clearWarnings();

        } catch (SQLException sqle) {
            // No FFDC coded needed

            // The SQLException could be a StaleConnectionException or a normal SQLException
            // resulted from invalid SQLStatement.

            ResourceException resX = AdapterUtil.translateSQLException(sqle, this, false, getClass()); // no need to fire connection event here, J2C handles on pretest failure

            // Remove this if the helpers ever start tracing exceptions.
            if (isTraceOn && tc.isDebugEnabled()) 
                Tr.debug(this, tc, "validate", AdapterUtil.getStackTraceWithState(sqle));

            if (helper.isConnectionError(sqle)) {
                // Don't do any cleanup
                if (isTraceOn && tc.isEntryEnabled())
                    Tr.exit(this, tc, "validate", false);
                return false;
            } else if (!(resX instanceof SecurityException)) {
                // This exception is due to an invalid SQL statement.

                //  - if currentAutoCommit is false, rollback the connection.
                if (!currentAutoCommit) {
                    try {
                        if (isTraceOn && tc.isDebugEnabled()) 
                            Tr.debug(this, tc, "AutoCommit is false.  Rolling back the connection after validation");
                        sqlConn.rollback();
                    } catch (SQLException rollbackEx) {
                        // No FFDC coded needed

                        // There is a possiblility that now the connection is stale.

                        if (helper.isConnectionError(rollbackEx)) {
                            if (isTraceOn && tc.isEntryEnabled())
                                Tr.exit(this, tc, "validate", AdapterUtil.getStackTraceWithState(rollbackEx));
                            return false;
                        }
                    }
                }

                try {
                    mcf.helper.doConnectionCleanup(sqlConn);
                } catch (SQLException cleanEx) {
                    // No FFDC coded needed

                    // There is a possiblility that now the connection is stale.

                    if (helper.isConnectionError(cleanEx)) {
                        if (isTraceOn && tc.isEntryEnabled())
                            Tr.exit(this, tc, "validate", AdapterUtil.getStackTraceWithState(cleanEx));
                        return false;
                    }
                }

                try {
                    sqlConn.clearWarnings();
                } catch (SQLException cleanEx) {
                    // No FFDC coded needed

                    // There is a possibility that now the connection is stale.

                    if (helper.isConnectionError(cleanEx)) {
                        if (isTraceOn && tc.isEntryEnabled())
                            Tr.exit(this, tc, "validate", AdapterUtil.getStackTraceWithState(cleanEx));
                        return false;
                    }
                }
            }
        }

        if (isTraceOn && tc.isEntryEnabled())
            Tr.exit(this, tc, "validate", true);
        return true;
    }
548281
@Override
	public SqlContext param(final String parameterName, final Object value, final int sqlType) {
		if (value instanceof Optional) {
			Optional<?> optionalValue = (Optional<?>) value;
			if (optionalValue.isPresent()) {
				param(new Parameter(parameterName, optionalValue.get(), sqlType));
			} else {
				param(new Parameter(parameterName, null));
			}
			return this;
		} else {
			return param(new Parameter(parameterName, value, sqlType));
		}
	}
394062
public static boolean isValidProcedureCall(String sql, Dialect dialect) {
		if (sql == null) {
			return false;
		}
		if (dialect instanceof OracleDialect) {
			return sql.split("\\?").length == 2;
		} else {
			return true;
		}
	}
6170
public static ResultSet callQuery(Connection conn, String sql, Object... params) throws SQLException {
		CallableStatement proc = null;
		try {
			proc = StatementUtil.prepareCall(conn, sql, params);
			return proc.executeQuery();
		} finally {
			DbUtil.close(proc);
		}
	}
548937
protected boolean isDbInitialized(final Connection con)
   {
      return SecurityHelper.doPrivilegedAction(new PrivilegedAction<Boolean>()
      {

         public Boolean run()
         {
            return JDBCUtils.tableExists(configTableName, con);
         }
      });
   }