SNOW-1892866: Named stages using spaces or special characters are not quoted in "PUT file" SQL statements

[TAGC]

This issue pertains to a possible bug in the implementation of SnowflakeConnectionV1.uploadStreamInternal. It seems that this method may not be appropriately wrapping the identifier for a named stage in single quotes when it contains spaces or special characters, as is required according to the Snowflake documentation:

Note
If the stage name or path includes spaces or special characters, enclose it in single quotes. For example, use '@"my stage"' for a stage named "my stage".

If you have time, I'd appreciate if this could be confirmed as a bug, or if we're not utilising the method correctly on our end.

Details are included below.

---

1. What version of JDBC driver are you using?

v3.20.0

2. What operating system and processor architecture are you using?

macOS M1 Pro

3. What version of Java are you using?

Java 17

4. What did you do?

In one of our applications, we utilise SnowflakeConnectionV1 via the following method:

  public void uploadToInternalStage(
      String stageName, String path, InputStream data, String fileName) throws SQLException {
    connection
        .unwrap(SnowflakeConnection.class)
        .uploadStream(stageName, path, data, fileName, false);
  }

We have the following named stages defined within a Snowflake database DATABASE_1 under schema PUBLIC:

• df_stage_name_lowercase
• df_STAGE_NAme_with_MIXEd_CASing
• df stage name with spaces
• df_stage_name_with_~???@_special_characters

We fully-qualify the stage name we pass to SnowflakeConnectionV1.uploadStream and quote each part of the identifier, e.g. "DATABASE_1"."PUBLIC"."df_stage_name_lowercase".

For the first two test cases, the files are successfully loaded to the named stage. However, for the named stages containing spaces or special characters, the SQL statement used to upload the files fails to execute. For the test case df stage name with spaces, we get the following exception logs:

25-01-08 16:33:44 agent-1  | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
2025-01-08 16:33:44 agent-1  | syntax error line 1 at position 61 unexpected 'name'.
2025-01-08 16:33:44 agent-1  | syntax error line 1 at position 66 unexpected 'with'.
2025-01-08 16:33:44 agent-1  | parse error line 1 at position 131 near '<EOF>'.

The root cause appears to be that SnowflakeConnectionV1.uploadStreamInternal does not wrap the entire identifier in single quotes when spaces or special characters are present. For this test case, the following SQL statement gets constructed:

put file:///tmp/placeholder @"DATABASE_1"."PUBLIC"."df stage name with spaces"/d6cf4234-2f17-40cb-a2c0-b68a729b16ad/ overwrite=true

Based on the Snowflake docs referenced above, I believe it ought to be:

put file:///tmp/placeholder '@"DATABASE_1"."PUBLIC"."df stage name with spaces"'/d6cf4234-2f17-40cb-a2c0-b68a729b16ad/ overwrite=true

5. What did you expect to see?

I expect that the Snowflake connector succeeds in uploading files to named stages regardless of whether there are spaces or special characters in the name.

6. Can you set logging to DEBUG and collect the logs?

I believe the logs included below should be sufficient to debug the issue, but let me know if more logs are required:

2025-01-08 16:33:44 agent-1  | Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
2025-01-08 16:33:44 agent-1  | syntax error line 1 at position 61 unexpected 'name'.
2025-01-08 16:33:44 agent-1  | syntax error line 1 at position 66 unexpected 'with'.
2025-01-08 16:33:44 agent-1  | parse error line 1 at position 131 near '<EOF>'.
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:176)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:109)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:490)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:396)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:498)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeFileTransferAgent.parseCommandInGS(SnowflakeFileTransferAgent.java:1299)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeFileTransferAgent.parseCommand(SnowflakeFileTransferAgent.java:919)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeFileTransferAgent.<init>(SnowflakeFileTransferAgent.java:895)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.DefaultSFConnectionHandler.getFileTransferAgent(DefaultSFConnectionHandler.java:381)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeConnectionV1.uploadStreamInternal(SnowflakeConnectionV1.java:959)
2025-01-08 16:33:44 agent-1  |  at net.snowflake.client.jdbc.SnowflakeConnectionV1.uploadStream(SnowflakeConnectionV1.java:864)

[sfc-gh-sghosh]

Hello @TAGC ,

Thanks for raising the issue, we are looking into it, will update.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @TAGC ,

Please use a variable with put command for the stage name

Example:
String stagename = "my stage space";
String str = "put file:///Users/sghosh/Documents/Traces/File24.csv '@"" + stagename + ""' auto_compress=false overwrite=true";
stmt.execute(str);

Output:
put file:///Users/sghosh/Documents/Traces/File24.csv '@"my stage space"' auto_compress=false overwrite=true
file uploaded

As per the documentation,
If the stage name or path includes spaces or special characters, enclose it in single quotes. For example, use '@"my stage"' for a stage named "my stage".

Regards,
Sujan

[TAGC]

Hi Sujan,

When you say do this:

String stagename = "my stage space";
String str = "put file:///Users/sghosh/Documents/Traces/File24.csv '@"" + stagename + ""' auto_compress=false overwrite=true";
stmt.execute(str);

The code that constructs the PUT SQL statement exists within this repo's codebase (as part of SnowflakeConnectionV1 ) rather than the consumer's codebase, so I'm not sure how I can do this.

[sfc-gh-sghosh]

Hello @TAGC ,

Could you clarify what you trying to say, since you need to use single quotes as the stage name contains special character, the above example I provided just from jdbc application client perspective, so you have to use the application logic like above. This is not a bug its a application usage issue.

Regards,
Sujan

[TAGC]

Hi there Sujan,

What I mean is that this library exposes the SnowflakeConnection interface as part of its API with the following method defined on it:

void uploadStream(
    String stageName,
    String destPrefix,
    InputStream inputStream,
    String destFileName,
    boolean compressData)
    throws SQLException;

As a consumer of this library, this method is our application's entry-point into this library. The actual construction of the PUT SQL statement is embedded within a private method of an implementation of this interface (SnowflakeConnectionV1), so as a consumer I have no way to control exactly how this statement is constructed.

Specifically - based on the Snowflake documentation that you referenced, and that I referenced in my original post - it seems like the SnowflakeConnectionV1 code below needs to be updated so that it checks if there are spaces or special characters in the stage name and, if so, prepend and append a single quote using the destStage StringBuilder:

StringBuilder destStage = new StringBuilder();

// add stage name
// pseudo-code: if containsSpecialCharacter(stageName) destStage.prepend("'");
if (!(stageName.startsWith("@") || stageName.startsWith("'@") || stageName.startsWith("$$@"))) {
  destStage.append("@");
}
destStage.append(stageName);

// add dest prefix
if (destPrefix != null) {
  if (!destPrefix.startsWith("/")) {
    destStage.append("/");
  }
  destStage.append(destPrefix);
}
// pseudo-code: if containsSpecialCharacter(stageName) destStage.append("'");

StringBuilder putCommand = new StringBuilder();
// use a placeholder for source file
putCommand.append("put file:///tmp/placeholder ");
putCommand.append(destStage.toString());
putCommand.append(" overwrite=true");

If this is an application usage issue rather than a bug, it would be helpful to provide an example of what arguments we can pass to SnowflakeConnection.uploadStream to ensure a valid PUT SQL command gets generated by this library. However, in the process of doing further testing, I discovered I can do the following hack in our own code to get this to work successfully for a named stage with spaces:

/**
 * Stage data to a path inside an Internal Stage.
 *
 * @param stageName Name of the stage to upload to.
 * @param path Path within the stage to upload to.
 * @param data Data to upload to the stage.
 * @param fileName File to upload data to.
 */
public void uploadToInternalStage(
    String stageName, String path, InputStream data, String fileName) throws SQLException {

  // Added this hack (note: also needs to check for special characters)
  if (stageName.contains(" ")) {
    stageName = "'@" + stageName; // + "'";
    path = path + "'";
  }

  connection
      .unwrap(SnowflakeConnection.class)
      .uploadStream(stageName, path, data, fileName, false);
}

In this case, SnowflakeConnectionV1 will generate the following SQL command:

put file:///tmp/placeholder '@"DATABASE_1"."PUBLIC"."df stage name with spaces"/df182950-2fbc-4e24-8f7a-61fd59c5fd0f/' overwrite=true

And this allows the file to be uploaded to the specified named stage:

As noted, however, this is a pretty hacky solution that's required in the consuming application, and so it may be worth updating SnowflakeConnectionV1 to check whether stageName contains spaces or special characters and append/prepend the quote characters to generate the equivalent PUT command as is shown above.