Win a copy of TensorFlow 2.0 in Action this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

How to escape all special characters?

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my server-side Java I want to escape all special characters so I can include the string in a MySQL search.



Kind regards,

Glyn
 
Rancher
Posts: 220
16
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use PreparedStatements - don't write your own logic.

Create a PreparedStatement with a question mark where you want to insert your parameter:


Insert the parameter into the statement:
 
Marshal
Posts: 25969
70
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
++ to what Zachary said. And if your application is designed so that what your server receives isn't just parameters, but some string which you hoped to insert into basic SQL, then you should really stop and redesign the application so that it doesn't do that. Otherwise your application is vulnerable to SQL injection attacks, which are a very common way of breaking into systems.

And no, the idea of escaping special characters doesn't work because a ' character can be part of the SQL markup (where name = 'xxx') or part of the data (O'Brien).
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul and Zachary,

I am most certainly using prepared statements. I will add more displays to see exactly where it is going wrong when I pass a special character to it.

Kind regards,

Glyn
 
Paul Clapham
Marshal
Posts: 25969
70
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, sometimes people "use" PreparedStatement but they don't use the part with the question marks and the "ps.setInt(1, number)" methods. Don't know if you did that though, just speculating. "Special" characters (whatever those might be) shouldn't make any difference in the normal use of PreparedStatement. But post back with more details if you're really stuck.
 
Saloon Keeper
Posts: 22678
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL doesn't really have any "special" characters except two.

The single-quote, which has to be escaped by doubling when it appears within a string literal. That is: 'O''Brian'.

The percent sign when used in a LIKE clause, and which likewise has to be escaped by doubling.

 
Saloon Keeper
Posts: 7395
66
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:The single-quote, which has to be escaped by doubling when it appears within a string literal. That is: 'O''Brian'.
The percent sign when used in a LIKE clause, and which likewise has to be escaped by doubling.


I don't think the first one is an issue when you used prepared statements and '?'. The second case should only apply to the WHERE clause (I'm guessing).
 
Tim Holloway
Saloon Keeper
Posts: 22678
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Carey Brown wrote:

Tim Holloway wrote:The single-quote, which has to be escaped by doubling when it appears within a string literal. That is: 'O''Brian'.
The percent sign when used in a LIKE clause, and which likewise has to be escaped by doubling.


I don't think the first one is an issue when you used prepared statements and '?'. The second case should only apply to the WHERE clause (I'm guessing).



No, you don't escape on a "?", but you do in a SQL string literal. For example: "SELECT FIRST_NAME, ADDRESS, CITY FROM PERSON WHERE LAST_NAME = 'O''Brian' AND COUNTY=?"

LIKE is only meaningful in a WHERE, but "%" doesn't work in a WHERE without a LIKE.
 
So I left, I came home, and I ate some pie. And then I read this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic