【JDBC】PrepareStatement的占位符不可用于表名、字段名，只可用于参数

【目的】

验证ps的占位符能否用于表名

【代码】

package com.hy.lab.timediff;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DbUtil {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public static Connection getConn() throws Exception{
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PSWD);
        return conn;
    }

    public static void main(String[] args){
        final String sql="select count(*） from ?";

        try(Connection conn=getConn();
            PreparedStatement ps=conn.prepareStatement(sql);
            ){

            ps.setString(1,"EMP76");
            ResultSet rs=ps.executeQuery();

            while(rs.next()){
                System.out.println(rs.getInt(1));
            }

            System.out.println("End");

        }catch(Exception ex){
            ex.printStackTrace();
        }
    }
}

输出：

C:\Java8\bin\java.exe "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.3.2\lib\idea_rt.jar=57399:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.3.2\bin" -Dfile.encoding=UTF-8 -classpath C:\java8\jre\lib\charsets.jar;C:\java8\jre\lib\deploy.jar;C:\java8\jre\lib\ext\access-bridge-64.jar;C:\java8\jre\lib\ext\cldrdata.jar;C:\java8\jre\lib\ext\dnsns.jar;C:\java8\jre\lib\ext\jaccess.jar;C:\java8\jre\lib\ext\jfxrt.jar;C:\java8\jre\lib\ext\localedata.jar;C:\java8\jre\lib\ext\nashorn.jar;C:\java8\jre\lib\ext\sunec.jar;C:\java8\jre\lib\ext\sunjce_provider.jar;C:\java8\jre\lib\ext\sunmscapi.jar;C:\java8\jre\lib\ext\sunpkcs11.jar;C:\java8\jre\lib\ext\zipfs.jar;C:\java8\jre\lib\javaws.jar;C:\java8\jre\lib\jce.jar;C:\java8\jre\lib\jfr.jar;C:\java8\jre\lib\jfxswt.jar;C:\java8\jre\lib\jsse.jar;C:\java8\jre\lib\management-agent.jar;C:\java8\jre\lib\plugin.jar;C:\java8\jre\lib\resources.jar;C:\java8\jre\lib\rt.jar;C:\Users\ufo\IdeaProjects\MybatisCli\target\classes;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot-starter\2.5.4\spring-boot-starter-2.5.4.jar;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot\2.5.4\spring-boot-2.5.4.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-context\5.3.9\spring-context-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-aop\5.3.9\spring-aop-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-expression\5.3.9\spring-expression-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot-autoconfigure\2.5.4\spring-boot-autoconfigure-2.5.4.jar;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot-starter-logging\2.5.4\spring-boot-starter-logging-2.5.4.jar;C:\Users\ufo\.m2\repository\ch\qos\logback\logback-classic\1.2.5\logback-classic-1.2.5.jar;C:\Users\ufo\.m2\repository\ch\qos\logback\logback-core\1.2.5\logback-core-1.2.5.jar;C:\Users\ufo\.m2\repository\org\apache\logging\log4j\log4j-to-slf4j\2.14.1\log4j-to-slf4j-2.14.1.jar;C:\Users\ufo\.m2\repository\org\apache\logging\log4j\log4j-api\2.14.1\log4j-api-2.14.1.jar;C:\Users\ufo\.m2\repository\org\slf4j\jul-to-slf4j\1.7.32\jul-to-slf4j-1.7.32.jar;C:\Users\ufo\.m2\repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;C:\Users\ufo\.m2\repository\org\yaml\snakeyaml\1.28\snakeyaml-1.28.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-core\5.3.9\spring-core-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-jcl\5.3.9\spring-jcl-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-jdbc\5.3.9\spring-jdbc-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-beans\5.3.9\spring-beans-5.3.9.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-tx\5.3.9\spring-tx-5.3.9.jar;C:\Users\ufo\.m2\repository\com\zaxxer\HikariCP\4.0.3\HikariCP-4.0.3.jar;C:\Users\ufo\.m2\repository\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;C:\Users\ufo\.m2\repository\com\alibaba\fastjson\1.2.48\fastjson-1.2.48.jar;C:\Users\ufo\.m2\repository\com\fasterxml\jackson\core\jackson-databind\2.9.8\jackson-databind-2.9.8.jar;C:\Users\ufo\.m2\repository\com\fasterxml\jackson\core\jackson-annotations\2.9.0\jackson-annotations-2.9.0.jar;C:\Users\ufo\.m2\repository\com\fasterxml\jackson\core\jackson-core\2.9.8\jackson-core-2.9.8.jar;C:\Users\ufo\.m2\repository\com\oracle\database\jdbc\ojdbc8\21.1.0.0\ojdbc8-21.1.0.0.jar;C:\Users\ufo\.m2\repository\com\oracle\database\nls\orai18n\21.1.0.0\orai18n-21.1.0.0.jar;C:\Users\ufo\.m2\repository\org\mybatis\mybatis\3.5.9\mybatis-3.5.9.jar;C:\Users\ufo\.m2\repository\org\apache\httpcomponents\httpclient\4.5.13\httpclient-4.5.13.jar;C:\Users\ufo\.m2\repository\org\apache\httpcomponents\httpcore\4.4.13\httpcore-4.4.13.jar;C:\Users\ufo\.m2\repository\commons-logging\commons-logging\1.2\commons-logging-1.2.jar;C:\Users\ufo\.m2\repository\commons-codec\commons-codec\1.11\commons-codec-1.11.jar;C:\Users\ufo\.m2\repository\redis\clients\jedis\2.9.0\jedis-2.9.0.jar;C:\Users\ufo\.m2\repository\org\apache\commons\commons-pool2\2.4.2\commons-pool2-2.4.2.jar;C:\Users\ufo\.m2\repository\org\apache\commons\commons-lang3\3.3.2\commons-lang3-3.3.2.jar com.hy.lab.timediff.DbUtil
java.sql.SQLSyntaxErrorException: ORA-00903: 表名无效

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:928)
    at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093)
    at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3847)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098)
    at com.hy.lab.timediff.DbUtil.main(DbUtil.java:29)
Caused by: Error : 903, Position : 23, Sql = select count(*） from :1 , OriginalSql = select count(*） from ?, Error Msg = ORA-00903: 表名无效

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
    ... 15 more

Process finished with exit code 0

【正确代码】

package com.hy.lab.timediff;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DbUtil {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public static Connection getConn() throws Exception{
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PSWD);
        return conn;
    }

    public static void main(String[] args){
        final String sql="select count(*） from EMP76";

        try(Connection conn=getConn();
            PreparedStatement ps=conn.prepareStatement(sql)){

            //ps.setString(1,"EMP76");
            ResultSet rs=ps.executeQuery();

            while(rs.next()){
                System.out.println(rs.getInt(1));
            }

            System.out.println("End");

        }catch(Exception ex){
            ex.printStackTrace();
        }
    }
}

输出：

5000000
End

【结论】

select count(*) from tablename; 这样的SQL，对于查表是否存在是容易理解、撰写方便、普适性广的手段；

如果因为SQL注入风险而舍弃，那未免太武断了，只要没有被用户输入的可能，那就不必害怕。

END