SQLException:连接字符串包含格式错误的名称或值 [英] SQLException: The connection string contains a badly formed name or value

问题描述

我尝试连接到MS SQL Server登台环境，但显示

I tried to connect to MS SQL Server staging environment but it displays

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

我的密码包含{和}

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

How to correctly escape the characters? My JDBC URL:

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

我尝试使用其他密码登录，但显示异常:

I try login with different password and it says Exception:

用户'WHOPQDTB_user'登录失败.

Login failed for user 'WHOPQDTB_user'.

请帮助.预先感谢.

推荐答案

转义连接URL中的值

由于包含特殊字符(例如空格，分号和引号)，您可能必须转义连接URL值的某些部分.如果将这些字符括在大括号中，则JDBC驱动程序支持对这些字符进行转义.例如，{;}转义为分号.

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.

因此，更改密码或将用户名/密码另存为单独的变量，然后将其添加到Connection上.

So change password or save the user/password as seperate variables and add it on Connection.

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

如果要使用属性集合，您将在这里找到属性名称:

Here you will find the Propertie Names if you want to use a properties collection:

Microsoft文档-设置连接属性

java2s-使用属性创建连接具有Java的一个很好的例子.它用于MySQL Server，但您只需要更改属性名称即可. (来自上面的链接页面)

java2s - Create Connection With Properties has a nice example for java. It is for a MySQL Server but you only have to change the propertie names. (from the linked Page above)

tl; dr 潜入源头

源代码-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(String Url, Properties suppliedProperties)
用途:

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

从给定的connectionUrl获取(额外)属性:

to get (extra) Properties from the given connectionUrl:

private Properties parseAndMergeProperties(String Url, Properties suppliedProperties)

正在使用:

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

并且在绕过此案例切换并直接转到"SQLServerConnection.java"的唯一方法是提供正确的属性集合！

The only way to bypass this Case-switch and get straight to "SQLServerConnection.java", is to deliver a proper Property Collection!

MSSQL-JDBC-SQLServerConnection.java

功能Connection connect(Properties propsIn, SQLServerPooledConnection pooledConnection)
分别Connection connectInternal(Properties propsIn, SQLServerPooledConnection pooledConnection):

Function Connection connect(Properties propsIn, SQLServerPooledConnection pooledConnection)
respectively 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);
}

这篇关于SQLException:连接字符串包含格式错误的名称或值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！