Mybatis bind element Oracle bin variable?

[Rashedul Hasan]

Hi All

I need to know if I use Mybatis bind element like the following:

<select id="selectBlogsLike" resultType="Blog">

  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />

  SELECT * FROM BLOG

  WHERE title LIKE #{pattern}

</select>

Will this bind element be treated as bind variable in Oracle?

Thanks

[Rashedul Hasan]

I actually mean Oracle Bind Variable not Bin Variable...sorry.

[Frank Martínez]

Hi Rashedul,

No, it will only create a jdbc parameter binding.

  SELECT * FROM BLOG

  WHERE title LIKE ?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Frank D. Martínez M.

[Rashedul Hasan]

Hi Frank

Thanks...could you please tell me how to do this from Mybatis? I need to pass a parameter which will be Oracle bind variable like below:

SELECT * FROM BLOG

WHERE title LIKE :bindVar

to avoid hard parse. 

Thanks

[Frank Martínez]

According to this: http://www.akadia.com/services/ora_bind_variables.html

JDBC Prepared statement parameters are equivalent to Bind variables in Oracle.

The next question is though, what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself?

In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.

For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature.

[Rashedul Hasan]

Got it..thanks....then what is the difference between these two formats in Mybatis:

<select id="selectBlogsLike" resultType="Blog">

  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />

  SELECT * FROM BLOG

  WHERE title LIKE #{pattern}

</select>

AND

<select id="selectBlogsLike" resultType="Blog">  

  SELECT * FROM BLOG

  WHERE title LIKE #{pattern}

</select>

It seems like both will be same. If so, what is the purpose of this BIND element? Thanks

[Frank Martínez]

In the first case, you can bind any expression inside the mapper. In the second case you have to calculate the expression in java and pass its result to the mapper. 

[Rashedul Hasan]

Thanks a lot.

[Manohar Parelly]

Hi, 

How safe it is to use <bind> variable to set parameter values with respect to SQL Injection attacks? I see <bind> variables allow us to set parameters during query preparation, same as preparing query manually with string concatenation.in Java.

Regards,

Manohar Parelly

[vladi...@gmail.com]

As a MyBatis user (not a developer) I would say that using <bind> is safe in respect to SQL injection.

The only potentially unsafe usage is when you type ${parameter}. Note the dollar sign ($) instead of the pound sign (#). Even with the dollar sign ($) you can still make it SQL-Injection-safe by satinizing the "parameter" value. That is, escaping orremoving special characters.