java - SQLException : The connection string contains a badly formed name or value

Question

我尝试连接到 MS SQL Server 临时环境，但它显示

SQLException: The connection string contains a badly formed name or value

其中，我的密码包含 { 和 }

如何正确转义字符？我的 JDBC URL:

jdbc:sqlserver://localhost;databaseName=WHOPQDTB_test;integratedSecurity=false;user=WHOPQDTB_user;password='ahsdgahgsd';

我尝试使用不同的密码登录，它显示异常:

Login failed for user 'WHOPQDTB_user'.

请帮忙。提前致谢。

Answer 1

Microsoft Docs - Building the Connection URL

转义连接 URL 中的值

You might have to escape certain parts of the connection URL values because of the inclusion of special characters such as spaces, semicolons, and quotation marks. The JDBC driver supports escaping these characters if they are enclosed in braces. For example, {;} escapes a semicolon.

Escaped values can contain special characters (especially '=', ';', '[]', and space) but cannot contain braces. Values that must be escaped and contain braces should be added to a properties collection.

因此，更改密码或将用户/密码保存为单独的变量并将其添加到连接上。

String dbURL = "jdbc:sqlserver://localhost\\sqlexpress";
String user = "sa";
String pass = "secret";
conn = DriverManager.getConnection(dbURL, user, pass);

如果您想使用属性集合，您将在此处找到属性名称:

Microsoft Docs - Setting the Connection Properties

java2s - Create Connection With Properties有一个很好的java例子。它适用于 MySQL 服务器，但您只需更改属性名称。 (来自上面的链接页面)

tl;博士深入探究源头

SourceCode - DriverManager.java

使用.getConnection(String url, String user, String password)将创建一个Properties条目。

@CallerSensitive
public static Connection getConnection(String url,
    String user, String password) throws SQLException {
    java.util.Properties info = new java.util.Properties();

    if (user != null) {
        info.put("user", user);
    }
    if (password != null) {
        info.put("password", password);
    }

    return (getConnection(url, info, Reflection.getCallerClass()));
}

mssql-jdbc 代码

MSSQL-JDBC - SQLServerDriver.java

public java.sql.Connection connect(字符串Url，提供的属性Properties)
用途:

// Merge connectProperties (from URL) and supplied properties from user.
Properties connectProperties = parseAndMergeProperties(Url, suppliedProperties);

从给定的连接 URL 获取(额外)属性:

私有(private)属性parseAndMergeProperties(字符串Url，属性提供的属性)

正在使用:

Properties connectProperties = Util.parseUrl(Url, drLogger);

在 MSSQL- Util.java是交易破坏者。

    if (ch == ';') {...}
case inEscapedValueStart: 
    if (ch == '}') {...}
case inEscapedValueEnd: 
    if (ch == ';') {...}

绕过此案例切换并直接进入“SQLServerConnection.java”的唯一方法是提供正确的属性集合!

MSSQL-JDBC - SQLServerConnection.java

函数连接connect(Properties propsIn, SQLServerPooledConnection pooledConnection)
分别 Connection connectInternal(Properties propsIn, SQLServerPooledConnection pooledConnection):

sPropKey = SQLServerDriverStringProperty.PASSWORD.toString();
sPropValue = activeConnectionProperties.getProperty(sPropKey);
if (sPropValue == null) {
    sPropValue = SQLServerDriverStringProperty.PASSWORD.getDefaultValue();
    activeConnectionProperties.setProperty(sPropKey, sPropValue);
}