escape character for '@' -- JDBC?

escape character java
escape characters python
escape character in c
escape character javascript
escape character online
escape characters c#
escape character list
python escape backslash

I'm making a batch insert to MySQL table:

insert into table1 (field1, field2) values("aa@gmail.com", "f2 value"), ("cc@gmail.com", "another f2 here");

giving error to the character '@' in the value String:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into buyers (field1, field2) values ('aa@' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)

How can i get around this - is there some kind of escape charactr for JDBC to work this?

Note: I'm aware of JDBC-batch execution. I'm looking for a solution for the above - if any:

pStat.addBatch();
pStat.executeBatch();

TIA.

Further note: The above insert query runs fine directly on MySQL without JDBC in between. Also note: this isn't an issue when JDBC itself sets up the parameter with pStat.getString("aa@gmail.com"); -- thus the batch execn is a solution.

I don't think the error message is indicating a problem with the '@' at sign character.

MySQL syntax error "right syntax to use near" usually points to the first token where the problem is encountered. In this case, it looks like MySQL is objecting to INSERT.

... near 'insert into buyers (field1, field2) values ('aa@' at line 1 at

I suspect that there is something before that insert in the SQL text, and MySQL is seeing multiple statements. That's just a guess, we're not seeing the actual code.

I recommend displaying the actual contents of the SQL text, before it's executed or prepared.

2.4.1 String literals, Escaping, special characters. As we've seen, a backslash \ is used to denote character classes, e.g. \d . So it's a special character in regexps  In computing and telecommunication, an escape character is a character that invokes an alternative interpretation on subsequent characters in a character sequence. An escape character is a particular case of metacharacters. Generally, the judgement of whether something is an escape character or not depends on the context.

Try using PreparedStatement. It resolves special characters automatically and avoids sql-injection.

String queryStr = "insert into table1 (field1, field2) values(?, ?);"
try {
    PreparedStatement preparedStatement = conn.prepareStatement(queryStr);
    preparedStatement.setString(1, "aa@gmail.com");
    preparedStatement.setString(2, "f2 value");
    preparedStatement.executeUpdate();
} catch (SQLException e) {
    // Error
} finally {
    if (preparedStatement != null) {
        preparedStatement.close();
    }
    if (conn != null) {
        conn.close();
    }
}

More examples: https://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/

Escaping, special characters, You can find the full list here. \t Insert a tab in the text at this point. \b Insert a backspace in the text at this point. \n Insert a newline in the text at  Good Coding Practice. It is good coding practice to avoid the need for URL escape characters. As a rule of thumb, avoid using the special characters above when formulating a URI string (filename), and I recommend using the hyphen (-) instead of the underscore (_) (as all search engines recognize the hyphen as a space separator,

Use single quotes:

insert into table1 (field1, field2) 
  values('aa@gmail.com', 'f2 value'), ('cc@gmail.com', 'another f2 here');

What are all the escape characters?, Character escapes in markup. You can use a character escape to represent any Unicode character in HTML, XHTML or XML using only ASCII characters. Different  The Basic Multilingual Plane is the unicode values from 0x0000 - 0xFFFF (0 - 65535). Additional planes can only be specified in Java by multiple characters: the egyptian heiroglyph A054 (laying down dude) is U+1303F / 𓀿 and would have to be broken into "\uD80C\uDC3F" (UTF-16) for Java strings.

Use the UTF-8 code for special characters when running from Java. UTF-8 code for @ is \u0040:

insert into table1 (field1, field2) values("aa\u0040gmail.com", "f2 value"), ("cc\u0040gmail.com", "another f2 here");

Using character escapes in markup and CSS, Escape Characters¶. The recognized escape sequences are: \newline: Ignored. \: Backslash (\); ': Single quote ('); ": Double quote (“); \a: ASCII Bell (BEL)  If you intend to "nest" commands with escaped characters, you may need to escape the escape character itself too. In general, that won't make it any easier to read or debug your batch files, however. Since the introduction of delayed variable expansion a new challenge is to escape exclamation marks, the "delayed" version of the percent sign.

Was doing two queries separated by ; in one. all resolved. nothing wrong with @.

Thanks for the insightful comments&answers.

Escape Characters, JSON String Escape / Unescape. Escapes or unescapes a JSON string removing traces of offending characters that could prevent parsing. The following  An escape sequence is regarded as a single character and is therefore valid as a character constant. Escape sequences are typically used to specify actions such as carriage returns and tab movements on terminals and printers.

Free Online JSON Escape / Unescape Tool, An escape character is a backslash \ followed by the character you want to insert. An example of an illegal character is a double quote inside a string that is  The ^ escape character can be used to make long commands more readable by splitting them into multiple lines and escaping the Carriage Return + Line Feed (CR/LF) at the end of a line: Mark Yocom [MSFT] has more on this technique here.

Python Escape Characters, To represent a newline character, single quotation mark, or certain other characters in a character constant, you must use escape sequences. Escape characters, Delimiters and Quotes. The PowerShell escape character is the grave-accent(`) The escape character can be used in three ways: 1) When used at the end of a line, it is a continuation character - so the command will continue on the next line.

Escape Sequences, Escaping a single metacharacter with a backslash works in all regular expression flavors. Some flavors also support the \Q…\E escape sequence. All the  identifier characters can be encoded by using UTF-16 character escape sequences; the escaped identifiers must still be from the legal character sets - you cannot define an identifier containing a dot, etc. numbers, operators, and punctuation cannot be escaped (e.g. 1.0f, etc. cannot be escaped)

Comments
  • Is the error in the insert itself? Have you tried brackets?
  • @DanielMarcus pls see "Further note." thx for the comment.
  • Thanks @xavierz that makes it a jdbc issue not a sql one
  • Does it help to use single quotes instead of double quotes: values('aa@gmail.com', 'f2 value'), ...?
  • @MarkusPscheidt no - same error.
  • then how come it's running fine on SQL scrapbook. i'm printing the query to console - copy/pasting to scrapbook to run.
  • besides-- the line doesn't end there at '@'.
  • @xavierz: I can't explain the behavior that you are observing; and it's not possible for me to reproduce the behavior. What I'm suggesting is that you are barking up the wrong tree in terms of JDBC and the '@' character. It should be fairly straightforward to test with field1 values that don't include the '@' character.
  • @xavierz: my suspicion is that there is more than one SQL statement in the query. We would expect this behavior if the SQL text was INSERT INTO sometable ( ... ) VALUES (...),(...); insert into buyers ... MySQL would flag an error at the second insert, because that's a second statement. It's not allowed to run more than one SQL statement in a query. (Again, I'm just guessing, because there is no code shown.)
  • If the problem is with the @ at sign character in the statement, I don't think that's a JDBC issue. At least, I've never encountered that issue before. I suspect that it's something else that's munging the string, before the string gets passed to JDBC.(Again, without seeing the actual code that's being executed, we're just guessing.)
  • Best practice for SQL is to use single quotes around string literals, but MySQL does allow for double quotes around string literals if ANSI_QUOTES is not included in sql_mode.