Sql Injection in java

[prem2]

Dear all,

I have using db operations such as insert,update,select,delete in my program .I have perform this operation using the prepared statement.

How to avoid the sql injection in my java program.?

*)I want to know functions to avoid the escape characters in java ?

*)Did any one know what are ways to implement the sql injection in java?

In php ,
We use addslashes, mysql_real_escape_string ,mysql_escape_string etc.

Thanks in advance

Thank you,

With Regards,
Prem

[masijade]

If you are using preparedstatement properly you are already preventing sql injection.

[prem2]

Thank you masijade,

Is their any functions to escape strings in java.
In php we are using mysql_real_escape_string or mysql_escape_string.

Thank you for your response

[masijade]

The PreparedStatement does that automatically when you use setString. I believe I just hinted at that.

[prem2]

dear masijade,
Kindly check the below code.

prepst.java

import java.sql.*;
import java.util.*;
import java.io.*;

public class prepst{
	//UPDATE PROGRAM FOR PREPARED STATEMENT
  public static void main(String[] args) {
    System.out.println("prepared statement example!\n");
    Connection con = null;
    PreparedStatement prest;
	String driver="com.mysql.jdbc.Driver";
	String url="jdbc:mysql://localhost:3306/";
	String database="test";
	String username="root";
	String password="password";
    try{
      Class.forName(driver);
      con = DriverManager.getConnection(url+database,username,password);
	  System.out.println("Connected To DataBase");
		  try{
			String sql = "UPDATE sample SET name = ? WHERE  id= ?";
			prest = con.prepareStatement(sql);
			String namevalue = "prem\n\nth";
			prest.setString(1,namevalue);
			prest.setInt(2,15);
			int affected_rows = prest.executeUpdate();
			System.out.println("Affected rows is "+affected_rows);
				if(affected_rows == 0){
					System.out.println("No update Occured");
					}
			}
		  catch (SQLException s){
			System.out.println("SQL statement is not executed!");
		  }
	  	  con.close();
    }
    catch (Exception e){
		System.out.println("Could not connect to database");
      e.printStackTrace();
    }
  }
}

In mysql table Displays:

mysql> SELECT * FROM sample;
+----+----------+------+
| id | name | COST |
+----+----------+------+
| 15 | prem

th | 110 |
+----+----------+------+
1 row in set (0.00 sec)

If i use the add slashes then insert will be in correct format.

Thank you,

With Regards,
Prem

[masijade]

How is that the "wrong" format? \n is newline so you have two newlines in that string and that is exactly what you see in the db so what's wrong?

[mKorbel]

for some of us is SqlInjection == Query execute SubQuerries,

but for some of us is SqlInjection == execute malicious code by using Sql Statements :-), too

[prem2]

Dear masijade,
How to avoid those \n string in the input during inserting into the database in java.

In php i use mysql_escape_string .This function is used to escape the characters.

How to do the same thing in java.
I need a function which is used to escape the special characters.

Thank you,

With Regards,
Prem

[masijade]

Okay. Thats something different. Now, what are you trying to do? If you don't want the resulting DB entry to have newlines, that is something completely different from "escaping special characters". "Escaping special characters" in a db context means making it so that the data will be inserted into the DB without some extra sql action taking place due to the content of the string (including simply causing a syntax error). So, what do you want? If you want to remove newlines and the such, then remove them, use something like replace("\\n", "") If you want some generic method that removes all "special" characters (newline, etc), no there isn't one, AFAIK.

[prem2]

Dear masijade,
So , I we want to remove the special characters like \n etc .We must use the str_replace functions in java. Because java does not contain any special characters removing functionality. Thank you once again.

Thank you ,

Regards,
Prem

[Taywin]

@prem2

masijade has already mentioned the replace() method of String class. You can use that to replace the string. For example, "d00000\n00000d".replace("\\n", "") will result in "d0000000000d" string.

[prem2]

Yes i accepting that . My suggestion is that would be better that if we have a default functions to avoid the special characters in the bunch of string .

Thank you
prem

[Taywin]

You could implement a sanitize method to deal with any query string; however, I would not recommend that. A better strategy is white-list which is to reject the query if something is wrong with it instead of try to clean it. Though, you may do it the way you want. :)