CODEWITHSUNDEEP
javasql-serverautomated-tests
nicholas
nicholas

Reputation: 2772

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

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

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

Where, my password contains { and }

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:

Login failed for user 'WHOPQDTB_user'.

Please help. Thanks in advance.

Upvotes: 1

Views: 4229

Answers (1)

CaTeR
CaTeR

Reputation: 301

Microsoft Docs - Building the Connection URL

Escaping Values in the Connection 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.

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 Docs - Setting the Connection Properties

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 Dive in the Source

SourceCode - DriverManager.java

the use of .getConnection(String url, String user, String password) will create a Properties entry.

@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()));
}

The mssql-jdbc Code

MSSQL-JDBC - SQLServerDriver.java

public java.sql.Connection connect(String Url, Properties suppliedProperties)
uses:

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

to get (extra) Properties from the given connectionUrl:

private Properties parseAndMergeProperties(String Url, Properties suppliedProperties)

is using:

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

And in MSSQL- Util.java are the dealbreaker.

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

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

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);
}

Upvotes: 3

Related Questions