placeholder, SQL injection?

These two days in class by classmate took a piece of code asked me, this code has what problem, I read a will say: Connection and PreparedStatement are not closed. He said more than that, as well as SQL injection, I was adamant that there was no SQL injection in place of the placeholder, but he raised a situation that seemed to me to be a reasonable point.

pstmt = conn.preparestatement ("Delete from user where user.id=?");  Pstmt.setstring (1, "w");

He thinks that if you write the code, there's an injection problem.

pstmt = conn.preparestatement ("Delete from user where user.id=?");  Pstmt.setstring (1, "w ' or ' 2 ' = ' 2");  

At that time I watched only to tell him there must be no injection problem, because in my mind I always remember is to use the placeholder can solve the injection problem, as to how to solve the don't know, see the above code is also very reasonable, feel setstring after the SQL statement should be

Delete from user where user.id= ' W ' or ' 2 ' = ' 2 ';  

　　

Back to the dorm I specifically wrote a program test, it turns out that we do not want to think of this, it is true that there is no injection problem with the placeholder, so the explanation is to escape some characters at the time of execution, but where the escape process is escaped, the above SQL statements run on the MySQL console, Look at the data to see all the data are deleted, that can only be explained in the Java program in transit, so I went to see Java source code, found in Java source PreparedStatement is just an interface, and there is no sub-class interface, I am very puzzled, did not realize how to use? So there must be an implementation of the place, to check the Internet, JDK directly provide interface, and the implementation is implemented by the database vendors, we use is the database vendor implementation of the class. So I went to check the MySQL jar package source code, found that there is a preparedstatement to achieve the JDK preparedstatement. The SetString method is implemented as follows:

 Public voidSetString (intParameterindex, String x)throwsSQLException {//if the passed string is null and then set this column to null        if(x = =NULL) {setnull (Parameterindex, Types.char); } Else{stringbuffer buf=NewStringBuffer ((int) (X.length () * 1.1)); Buf.append (‘\‘‘); intStringlength =x.length (); //            //Note:buf.append (char) is _faster_ than//appending in blocks, because the block//append requires a system.arraycopy () ....//Go figure ...//             for(inti = 0; i < stringlength; ++i) {Charc =X.charat (i); Switch(c) { Case0:/*must is escaped for ' MySQL '*/Buf.append (‘\\‘); Buf.append (' 0 ');  Break;  Case' \ n ':/*must is escaped for logs*/Buf.append (‘\\‘); Buf.append (N);  Break;  Case' \ r ': Buf.append (‘\\‘); Buf.append (' R ');  Break;  Case‘\\‘: Buf.append (‘\\‘); Buf.append (‘\\‘);  Break;  Case‘\‘‘: Buf.append (‘\\‘); Buf.append (‘\‘‘);  Break;  Case‘"‘:/*Better safe than sorry*/                    if( This. Usingansimode) {Buf.append (‘\\‘); } buf.append (‘"‘);  Break;  Case' \032 ':/*This gives problems on Win32*/Buf.append (‘\\‘); Buf.append (Z);  Break; default: Buf.append (c); }} buf.append (‘\‘‘); String parameterasstring=buf.tostring (); byte[] Parameterasbytes =NULL; if(! This. Isloaddataquery) {Parameterasbytes=stringutils.getbytes (parameterasstring, This. Charconverter, This. charencoding, This. Connection. getservercharacterencoding (), This. Connection. Parserknowsunicode ()); } Else {                //Send with platform character encodingParameterasbytes =parameterasstring.getbytes ();        } setinternal (Parameterindex, parameterasbytes); }    }

At this time, you can find that the outermost single quote is escaped at setstring, which means that the SQL statement after setstring

    Delete from user where user.id= ' w\ ' or \ ' 2\ ' =\ ' 2 ';  

　　

And look closely will find in the setstring is a character parsing, the escape has escaped, as he wrote in a note better safe than sorry. So eventually, the placeholder does not have an injection problem.

Reproduced in http://blog.csdn.net/yan465942872/article/details/6753957

placeholder, SQL injection?