Bug against multiple params when using Java 8 -parameters to pass multi parameters

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug against multiple params when using Java 8 -parameters to pass multi parameters

Biao Huang
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Erwan Letessier
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Iwao AVE!
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Biao Huang
Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re[2]: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Guy Rouillier-2
Regarding your last point, ${} is string substitution; you can actually use it anywhere in the SQL statement, for example, the table name in the FROM clause.  #{} is parameter substitution, which means it is only allowed where parameters are valid.  So, ${} seems more susceptible to injection than #{}.  If your computer is suitably protected such that the only access to the MyBatis code is through your program, then Java will ensure that schoolId is an integer.

--
Guy Rouillier

------ Original Message ------
From: "Biao Huang" <[hidden email]>
Sent: 4/9/2018 6:48:20 PM
Subject: Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Biao Huang
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 6:56 GMT+08:00 Guy Rouillier <[hidden email]>:
Regarding your last point, ${} is string substitution; you can actually use it anywhere in the SQL statement, for example, the table name in the FROM clause.  #{} is parameter substitution, which means it is only allowed where parameters are valid.  So, ${} seems more susceptible to injection than #{}.  If your computer is suitably protected such that the only access to the MyBatis code is through your program, then Java will ensure that schoolId is an integer.

--
Guy Rouillier

------ Original Message ------
From: "Biao Huang" <[hidden email]>
Sent: 4/9/2018 6:48:20 PM
Subject: Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Biao Huang
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 10:47 GMT+08:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 6:56 GMT+08:00 Guy Rouillier <[hidden email]>:
Regarding your last point, ${} is string substitution; you can actually use it anywhere in the SQL statement, for example, the table name in the FROM clause.  #{} is parameter substitution, which means it is only allowed where parameters are valid.  So, ${} seems more susceptible to injection than #{}.  If your computer is suitably protected such that the only access to the MyBatis code is through your program, then Java will ensure that schoolId is an integer.

--
Guy Rouillier

------ Original Message ------
From: "Biao Huang" <[hidden email]>
Sent: 4/9/2018 6:48:20 PM
Subject: Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Iwao AVE!
Thanks for the demo, Biao!

In your test, the method takes only one parameter, so it's different from the issue reported here.
#{} is evaluated by MyBatis and when the sole parameter is simple type (i.e. not a bean), you can reference the parameter by any name (e.g. #{value}, #{xyz}, etc.).
${}, on the other hand, is evaluated by OGNL and to reference the parameter object itself, you need to use the name ${_parameter}.

Regards,
Iwao



2018-04-10 11:49 GMT+09:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 10:47 GMT+08:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 6:56 GMT+08:00 Guy Rouillier <[hidden email]>:
Regarding your last point, ${} is string substitution; you can actually use it anywhere in the SQL statement, for example, the table name in the FROM clause.  #{} is parameter substitution, which means it is only allowed where parameters are valid.  So, ${} seems more susceptible to injection than #{}.  If your computer is suitably protected such that the only access to the MyBatis code is through your program, then Java will ensure that schoolId is an integer.

--
Guy Rouillier

------ Original Message ------
From: "Biao Huang" <[hidden email]>
Sent: 4/9/2018 6:48:20 PM
Subject: Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Biao Huang
Thanks Harawata,
you are right, the demo is different from the original question due to the error also occurred when only with one parameter, so I simplified the demo to reproduce the issue.

2018-04-11 3:33 GMT+08:00 Iwao AVE! <[hidden email]>:
Thanks for the demo, Biao!

In your test, the method takes only one parameter, so it's different from the issue reported here.
#{} is evaluated by MyBatis and when the sole parameter is simple type (i.e. not a bean), you can reference the parameter by any name (e.g. #{value}, #{xyz}, etc.).
${}, on the other hand, is evaluated by OGNL and to reference the parameter object itself, you need to use the name ${_parameter}.

Regards,
Iwao



2018-04-10 11:49 GMT+09:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 10:47 GMT+08:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 6:56 GMT+08:00 Guy Rouillier <[hidden email]>:
Regarding your last point, ${} is string substitution; you can actually use it anywhere in the SQL statement, for example, the table name in the FROM clause.  #{} is parameter substitution, which means it is only allowed where parameters are valid.  So, ${} seems more susceptible to injection than #{}.  If your computer is suitably protected such that the only access to the MyBatis code is through your program, then Java will ensure that schoolId is an integer.

--
Guy Rouillier

------ Original Message ------
From: "Biao Huang" <[hidden email]>
Sent: 4/9/2018 6:48:20 PM
Subject: Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Iwao AVE!
Okay. So, could you change the demo to reproduce the following error which involves multiple parameters?

> Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]

I'm curious to know how it happens.

Regards,
Iwao

2018-04-11 13:28 GMT+09:00 Biao Huang <[hidden email]>:
Thanks Harawata,
you are right, the demo is different from the original question due to the error also occurred when only with one parameter, so I simplified the demo to reproduce the issue.

2018-04-11 3:33 GMT+08:00 Iwao AVE! <[hidden email]>:
Thanks for the demo, Biao!

In your test, the method takes only one parameter, so it's different from the issue reported here.
#{} is evaluated by MyBatis and when the sole parameter is simple type (i.e. not a bean), you can reference the parameter by any name (e.g. #{value}, #{xyz}, etc.).
${}, on the other hand, is evaluated by OGNL and to reference the parameter object itself, you need to use the name ${_parameter}.

Regards,
Iwao



2018-04-10 11:49 GMT+09:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 10:47 GMT+08:00 Biao Huang <[hidden email]>:
I uploaded a Unit Test to reproduce the issue, which can be accessible via https://github.com/xtuer/MyBatis-Issue-Unittest

2018-04-10 6:56 GMT+08:00 Guy Rouillier <[hidden email]>:
Regarding your last point, ${} is string substitution; you can actually use it anywhere in the SQL statement, for example, the table name in the FROM clause.  #{} is parameter substitution, which means it is only allowed where parameters are valid.  So, ${} seems more susceptible to injection than #{}.  If your computer is suitably protected such that the only access to the MyBatis code is through your program, then Java will ensure that schoolId is an integer.

--
Guy Rouillier

------ Original Message ------
From: "Biao Huang" <[hidden email]>
Sent: 4/9/2018 6:48:20 PM
Subject: Re: Bug against multiple params when using Java 8 -parameters to pass multi parameters

Thanks every one for your reply.
I'm sure no plugin used. The problem occurred when using ${theSameParamNameNotOnlySchoolIdMultipleTimesInAStatment} with Java 8 -parameters param. It works fine using @Param("schoolId") instead of Java 8 -parameters to pass multiple parameters in form ${paramName} to MyBatis.
Using #{schoolId} instead of ${schoolId} is a good suggestion. What I want to using ${schoolId} is that makes the output SQL query is more readable than using #{schoolI} due to make sure the schoolId is an integer, which cannot result in SQL injection.

2018-04-09 23:56 GMT+08:00 Iwao AVE! <[hidden email]>:
Hi Biao,

As Erwan suggested, you should use #{} instead of ${} in this case.

Regarding the error, I have no idea what is going on.
Are you using any plugin?
If no plugin is involved, please create a test case or example project and upload it to your GitHub repo. Then I'll look into it.

Regards,
Iwao

2018-04-08 18:11 GMT+09:00 Erwan Letessier <[hidden email]>:
Well in this case, using  #{schoolId} is what you should do: bind parameter instead of hard writing their values in the query.
When you make it work, you change from $ to # for all parameters or only schoolId occurrences?


On Sun, Apr 8, 2018, 05:59 Biao Huang <[hidden email]> wrote:
## MyBatis version
3.4.6

## Database vendor and version
MySQL 5.7.21

## Test case or example project

## Steps to reproduce
1. Using the param -parameters to javac when compile the project in order to compile the method's parameter names in to the byte code of the compiled class file.
So that we can pass multiple parameters to MyBatis without the @Param annotation in the Mapper interface to make the source code clean.

2. Write Mapper such as 
public interface ClazzMapper {
    List<Student> findStudentsBySchoolId(long schoolId, int offset, int count);
}

<mapper namespace="ebag.mapper.SchoolMapper">
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.school_id AS school_id,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.id AS clazz_id
FROM clazz_student AS cs
LEFT JOIN user ON user.school_id = ${schoolId} AND user.id = cs.student_id
        LEFT JOIN clazz ON clazz.school_id = ${schoolId} AND clazz.id = cs.clazz_id
    WHERE cs.school_id = ${schoolId} LIMIT ${offset}, ${count};
</select>
</mapper>

Please note ${schoolId} is used 3 times
## Expected result
Execute the SQL correctly.

## Actual result
The following error was prompted:
Parameter 'schoolId' not found. Available parameters are [0, 1, 2, 3, param3, param4, param1, param2]...

It works fine when change ${schoolId} to #{schoolId}

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
=====================================
QQ:     26664141
Skype: biao.mac
EMail:  [hidden email]

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.