1

I know there are a lot of questions specifically asking this but I'm unsure in my case. I'm using Spring Batch and a FieldSetMapper so I'm generating these statements as strings, and collecting these strings and passing them to my ItemWriter. In this sense, I'm not looking to use a prepared statement. Here's what I've got:

private static String insertStmt = "insert into ..." 
//this is the beginning of the insert statement. Not gonna type it all out

private String addStatement() {

    String values = String
            .format("values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ESCAPE '\'')",
                    this.primCustNum, this.primCustName, this.mfstRptId,
                    this.shtlStpNum, this.primCustAddr1,
                    this.primCustAddr2, this.primCustCity,
                    this.primCustState, this.primCustPostalCde,
                    this.primCustPhoneNum);

    //log.info("INSERT+VALUES: {}", insertStmt + values);
    return insertStmt + values;

}

but this won't handle a case like insert into ... where values ('tony's place', ...); because tony's has an apostraphe

Mike Henke
  • 641
  • 2
  • 10
  • 24

2 Answers2

2

Look this answer: How to escape single quotes for SQL insert...when string to insert is in a user generated variable

You need put two quotes before you send the insertStmt to insert, like this:

  • this.primCustNum = this.primCustNum.replace("'","''");
Community
  • 1
  • 1
Thiago Cavalcanti
  • 71
  • 3
-1

Just create another private method that takes your string as argument and iterate it and if detect special char, it will put a backslash before the char and return new string (with these backslashes). Then, during creating sql statement, instead of plain text, invoke this private method with your text as argument.

Rafał P
  • 252
  • 1
  • 8
  • That won't work because in SQL you escape an apostrophe by using two apostrophes, e.g., https://support.microsoft.com/en-us/kb/156501. – Lew Bloch Aug 01 '16 at 20:59