How to avoid funny character and sanitize before being inserted into db?

Question

I have the following code snippet where I check a soap results and insert data into my database.

Connection dbconn = null;
    Statement stmt1 = null;
    Statement stmt2 = null;
    try
    {
        dbconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", "tes1", "te15");
        stmt1 = dbconn.createStatement();
        stmt2 = dbconn.createStatement();
        DateFormat outDf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = Calendar.getInstance().getTime();

        String value = null;
        for (int i = 0; i < list.getLength(); i++)
        {
           Element eElement = (Element) e.getChildNodes();
            String Contents = eElement.getElementsByTagName("Content").item(0).getTextContent();  
            String insertCommand = "INSERT INTO command SET content='"+content+"'";
            System.out.println("\n SET INSERT :" + insertCommand);
            int count = stmt1.executeUpdate(insertCommand);
        }

    }

What I notice is like this as the content DCN5�716732412? So when I used this content for the next process I get a bad request error. How can I sanitize if the content had funny character to avoid in the first place itself?

@the problem now it goes into my db? How can I sanitize my content before allowing in? — user5313398, Dec 29 '16 at 19:13
I want to allow only normal character, numbers and comma and hash and other I think I should totally knock it out — user5313398, Dec 29 '16 at 19:15
Though using Prepared Statements is a good and recommended practice, you may encounter such issues even when they are used (setting values in invalid encoding). You have to align the data encoding when you get your data into your variable `Contents` with MySQL database encoding. — lospejos, Dec 29 '16 at 19:16
@lospejos I am not too sure on the encoding and how enable it in my java codes to avoid not allow characters? — user5313398, Dec 29 '16 at 19:17

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

You need to use a prepared statement, as suggested by @Gurwinder Singh,

Follow the example from this other answer on StackOverflow, or on this page on the OWASP site (https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet), which will show you how to do this safely, but in short is something like the following:

 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( );

If you want to allow characters that ARE unicode characters, you'll need to use a unicode connection string and table, i think, and make sure your tables are set to store unicode as well - which if you want to support asian characters, you'll probably need to anyway.

make sure you tell JDBC which encoding to use. This is done as part of the query string when connecting to the DB. This is the key part: ?useUnicode=yes&characterEncoding=UTF-8.

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?useUnicode=yes&characterEncoding=UTF-8","username", "password");
Ensure your table uses UTF-8. I usually go with "Encoding: UTF-8 Unicode(utf8)" and "Collation: utf8_bin"

Snagged from the above link.

edited May 23 '17 at 12:25

Community

1
1

answered Dec 29 '16 at 19:23

Brad Parks

66,836
64
257
336

so will this solve the problem in total or there will be still some exception with prepared statement? But since my codes are very length for now if I need to use a simple sanitisation is that possible? – user5313398 Dec 29 '16 at 19:27
I think it should work regardless - i've never had a problem, but if you want to sanitize as well, try and use a regex, like [in this example](http://stackoverflow.com/a/1184185/26510) – Brad Parks Dec 29 '16 at 19:34
How about this "Though using Prepared Statements is a good and recommended practice, you may encounter such issues even when they are used (setting values in invalid encoding). You have to align the data encoding when you get your data into your variable Contents with MySQL database encoding" What is lospejos suggesting here? – user5313398 Dec 29 '16 at 19:35
If you want to allow characters that ARE unicode characters, you'll need to [use a unicode connection string and table, i think](https://coderwall.com/p/rvduyw/jdbc-inserting-unicode-utf-8-characters-into-mysql), and make sure your tables are set to store unicode as well - which if you want to support asian characters, you'll probably need to anyway. – Brad Parks Dec 29 '16 at 21:18

How to avoid funny character and sanitize before being inserted into db?

1 Answers1

Linked