Bulletproof string escaping

[Mariano Lopez-Gappa]

Is there any further escaping besides .replace("'", "''") needed when building a query with a string?

Ex:

mySqlString = "SELECT * FROM users WHERE usr_name = '"+myName.replace("'", "''")+"'";

I have read this way of building queries is not recommended, but for the application I'm building this isn't a priority right now. You may suggest so anyway.

Thanks!

[Akshat Dimri]

I don't know about any other way ..but your way certainly helped me.

[Paul Sturrock]

Just use a PreparedStatement. That way you don;t need to write any custom string escaping code.

[Tim McGuire]

as Paul said, Prepared Statements are the way to go.

this is from the Open Web Application Security Project web site:

The use of prepared statements (aka parameterized queries) is how all developers should first be taught how to write database queries. They are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.

and this is their example :

  
 String custname = request.getParameter("customerName"); // This should REALLY be validated too
 // perform input validation to detect attacks
 String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
 
 PreparedStatement pstmt = connection.prepareStatement( query );
 pstmt.setString( 1, custname); 
 ResultSet results = pstmt.executeQuery( );

  

OWASP Sql Injection Prevention cheatsheet

[Mariano Lopez-Gappa]

Thanks again I will use prepared statements from now on and I've bookmarked the cheat sheet as well.