From 0ee10a2194856c469b950d16b463a7f6b8b6903a Mon Sep 17 00:00:00 2001
From: Simon Brandhof <simon.brandhof@sonarsource.com>
Date: Sun, 16 Oct 2016 17:57:38 +0200
Subject: [PATCH] Add missing jdbcType attributes in user and permission SQL
 requests

---
 .../db/permission/AuthorizationMapper.xml     | 84 ++++++++++---------
 .../db/permission/GroupPermissionMapper.xml   | 18 ++--
 .../db/permission/UserPermissionMapper.xml    |  8 +-
 .../org/sonar/db/user/GroupMapper.xml         | 20 ++---
 .../org/sonar/db/user/UserGroupMapper.xml     |  6 +-
 5 files changed, 70 insertions(+), 66 deletions(-)

diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml
index 63043b8bf02..504dc552c31 100644
--- a/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml
@@ -98,21 +98,21 @@
     SELECT gr.resource_id
     FROM group_roles gr
     WHERE
-    gr.role=#{role}
+    gr.role=#{role,jdbcType=VARCHAR}
     and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
     and
     <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or ">
-      gr.resource_id=#{element}
+      gr.resource_id=#{element,jdbcType=BIGINT}
     </foreach>
     UNION
     SELECT p.id
     FROM user_roles ur
     INNER JOIN projects p on p.id = ur.resource_id
     WHERE
-    ur.role=#{role}
-    and ur.user_id=#{userId} and
+    ur.role=#{role,jdbcType=VARCHAR}
+    and ur.user_id=#{userId,jdbcType=BIGINT} and
     <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or ">
-      p.id=#{element}
+      p.id=#{element,jdbcType=BIGINT}
     </foreach>
   </select>
 
@@ -120,11 +120,11 @@
     SELECT gr.resource_id
     FROM group_roles gr
     WHERE
-    gr.role=#{role}
+    gr.role=#{role,jdbcType=VARCHAR}
     and gr.group_id is null
     and
     <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or ">
-      gr.resource_id=#{element}
+      gr.resource_id=#{element,jdbcType=BIGINT}
     </foreach>
   </select>
 
@@ -139,23 +139,23 @@
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL
         where
-        gr.role=#{role}
+        gr.role=#{role,jdbcType=VARCHAR}
         and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where
-        gu.user_id=#{userId}))
+        gu.user_id=#{userId,jdbcType=BIGINT}))
         UNION
         SELECT p.kee as root_project_kee
         FROM user_roles ur
         INNER JOIN projects p on p.id = ur.resource_id AND p.module_uuid IS NULL
         where
-        ur.role=#{role}
-        and ur.user_id = #{userId}
+        ur.role=#{role,jdbcType=VARCHAR}
+        and ur.user_id = #{userId,jdbcType=BIGINT}
       </when>
       <otherwise>
         SELECT p.kee as root_project_kee
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL
         where
-        gr.role=#{role}
+        gr.role=#{role,jdbcType=VARCHAR}
         and gr.group_id is null
       </otherwise>
     </choose>
@@ -168,23 +168,23 @@
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL
         where
-        gr.role=#{role}
+        gr.role=#{role,jdbcType=VARCHAR}
         and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where
-        gu.user_id=#{userId}))
+        gu.user_id=#{userId,jdbcType=BIGINT}))
         UNION
         SELECT p.uuid as root_project_uuid
         FROM user_roles ur
         INNER JOIN projects p on p.id = ur.resource_id AND p.module_uuid IS NULL
         where
-        ur.role=#{role}
-        and ur.user_id = #{userId}
+        ur.role=#{role,jdbcType=VARCHAR}
+        and ur.user_id = #{userId,jdbcType=BIGINT}
       </when>
       <otherwise>
         SELECT p.uuid as root_project_uuid
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL
         where
-        gr.role=#{role}
+        gr.role=#{role,jdbcType=VARCHAR}
         and gr.group_id is null
       </otherwise>
     </choose>
@@ -198,20 +198,24 @@
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL
         where
-        gr.role=#{role}
+        gr.role=#{role,jdbcType=VARCHAR}
         and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where
-        gu.user_id=#{userId}))
+        gu.user_id=#{userId,jdbcType=BIGINT}))
         UNION
         SELECT p.id as root_project_id
         FROM user_roles ur
         INNER JOIN projects p on p.id = ur.resource_id AND p.module_uuid IS NULL
-        where ur.role=#{role} and ur.user_id = #{userId}
+        where
+        ur.role=#{role,jdbcType=VARCHAR} and
+        ur.user_id = #{userId,jdbcType=BIGINT}
       </when>
       <otherwise>
         SELECT p.id as root_project_id
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL
-        where gr.role=#{role} and gr.group_id is null
+        where
+        gr.role=#{role,jdbcType=VARCHAR}
+        and gr.group_id is null
       </otherwise>
     </choose>
   </sql>
@@ -223,7 +227,7 @@
         FROM group_roles gr
         INNER JOIN groups_users gu on gu.group_id=gr.group_id
         INNER JOIN users u on u.id=gu.user_id
-        where u.login=#{userLogin} and gr.resource_id is null
+        where u.login=#{userLogin,jdbcType=VARCHAR} and gr.resource_id is null
         UNION
         SELECT gr.role
         FROM group_roles gr
@@ -232,7 +236,7 @@
         SELECT ur.role
         FROM user_roles ur
         INNER JOIN users u on u.id=ur.user_id
-        where u.login=#{userLogin} and ur.resource_id is null
+        where u.login=#{userLogin,jdbcType=VARCHAR} and ur.resource_id is null
       </when>
       <otherwise>
         SELECT gr.role
@@ -246,24 +250,24 @@
     SELECT p.kee
     FROM group_roles gr, projects p
     WHERE
-    gr.role=#{role}
+    gr.role=#{role,jdbcType=VARCHAR}
     and gr.group_id is null
     and gr.resource_id = p.id
     and
     <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or ">
-      p.kee=#{element}
+      p.kee=#{element,jdbcType=VARCHAR}
     </foreach>
     UNION
     SELECT p.kee
     FROM group_roles gr, projects root, projects p
     WHERE
-    gr.role=#{role}
+    gr.role=#{role,jdbcType=VARCHAR}
     and gr.group_id is null
     and gr.resource_id = root.id
     and p.root_uuid = root.uuid
     and
     <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or ">
-      p.kee=#{element}
+      p.kee=#{element,jdbcType=VARCHAR}
     </foreach>
   </select>
 
@@ -271,35 +275,35 @@
     SELECT p.kee
     FROM group_roles gr, projects p
     WHERE
-    gr.role=#{role}
+    gr.role=#{role,jdbcType=VARCHAR}
     and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
     and gr.resource_id = p.id
     and
     <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or ">
-      p.kee=#{element}
+      p.kee=#{element,jdbcType=VARCHAR}
     </foreach>
     UNION
     SELECT p.kee
     FROM group_roles gr, projects root, projects p
     WHERE
-    gr.role=#{role}
+    gr.role=#{role,jdbcType=VARCHAR}
     and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
     and gr.resource_id = root.id
     and p.root_uuid = root.uuid
     and
     <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or ">
-      p.kee=#{element}
+      p.kee=#{element,jdbcType=VARCHAR}
     </foreach>
     UNION
     SELECT p.kee
     FROM user_roles ur
     INNER JOIN projects p on p.id = ur.resource_id
     WHERE
-    ur.role=#{role}
-    and ur.user_id=#{userId}
+    ur.role=#{role,jdbcType=VARCHAR}
+    and ur.user_id=#{userId,jdbcType=BIGINT}
     and
     <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or ">
-      p.kee=#{element}
+      p.kee=#{element,jdbcType=VARCHAR}
     </foreach>
   </select>
 
@@ -308,21 +312,21 @@
     FROM groups_users gu
     INNER JOIN group_roles gr ON gr.group_id=gu.group_id
     WHERE
-    gr.resource_id=#{componentId}
-    AND gr.role=#{role}
+    gr.resource_id=#{componentId,jdbcType=BIGINT}
+    AND gr.role=#{role,jdbcType=VARCHAR}
     AND gu.user_id in
     <foreach collection="userIds" open="(" close=")" item="id" separator=",">
-      #{id}
+      #{id,jdbcType=BIGINT}
     </foreach>
     UNION
     SELECT ur.user_id
     FROM user_roles ur
     WHERE
-    ur.resource_id=#{componentId}
-    AND ur.role=#{role}
+    ur.resource_id=#{componentId,jdbcType=BIGINT}
+    AND ur.role=#{role,jdbcType=VARCHAR}
     AND ur.user_id IN
     <foreach collection="userIds" open="(" close=")" item="id" separator=",">
-      #{id}
+      #{id,jdbcType=BIGINT}
     </foreach>
   </select>
 </mapper>
diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml
index 683b2990d5d..fc3c7b8d2ea 100644
--- a/sonar-db/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml
@@ -19,7 +19,7 @@
     where
       groups.componentId in
       <foreach collection="componentIds" open="(" close=")" item="id" separator=",">
-        #{id}
+        #{id,jdbcType=BIGINT}
       </foreach>
     GROUP BY groups.permission, groups.componentId
   </select>
@@ -30,9 +30,9 @@
     (SELECT g.name as name
     FROM groups g
     INNER JOIN group_roles group_role ON group_role.group_id=g.id
-    AND group_role.role=#{permission}
+    AND group_role.role=#{permission,jdbcType=VARCHAR}
     <if test="componentId != null">
-      AND group_role.resource_id=#{componentId}
+      AND group_role.resource_id=#{componentId,jdbcType=BIGINT}
     </if>
     <if test="componentId == null">
       AND group_role.resource_id IS NULL
@@ -42,10 +42,10 @@
     SELECT #{anyoneGroup} as name
     FROM group_roles group_role
     where
-      group_role.role=#{permission}
+      group_role.role=#{permission,jdbcType=VARCHAR}
       AND group_role.group_id IS NULL
       <if test="componentId != null">
-        AND group_role.resource_id=#{componentId}
+        AND group_role.resource_id=#{componentId,jdbcType=BIGINT}
       </if>
       <if test="componentId == null">
         AND group_role.resource_id IS NULL
@@ -81,7 +81,7 @@
     left join projects p on gr.resource_id = p.id
     <where>
       <if test="query.searchQueryToSql != null">
-        and lower(gr.name) like #{query.searchQueryToSql} ESCAPE '/'
+        and lower(gr.name) like #{query.searchQueryToSql,jdbcType=VARCHAR} ESCAPE '/'
       </if>
       <!-- filter rows with group permissions -->
       <if test="query.withAtLeastOnePermission()">
@@ -90,10 +90,10 @@
           and gr.resource_id is null
         </if>
         <if test="query.componentUuid!=null">
-          and p.uuid=#{query.componentUuid}
+          and p.uuid=#{query.componentUuid,jdbcType=VARCHAR}
         </if>
         <if test="query.permission!=null">
-          and gr.permission=#{query.permission}
+          and gr.permission=#{query.permission,jdbcType=VARCHAR}
         </if>
       </if>
     </where>
@@ -117,7 +117,7 @@
         #{name}
       </foreach>
       <if test="projectId!=null">
-        AND sub.resourceId=#{projectId}
+        AND sub.resourceId=#{projectId,jdbcType=BIGINT}
       </if>
       <if test="projectId==null">
         AND sub.resourceId IS NULL
diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
index 901f66063c5..248b6f3c99b 100644
--- a/sonar-db/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
@@ -29,10 +29,10 @@
         and u.active = ${_true}
       </if>
       <if test="userLogins != null">
-        and u.login in <foreach collection="userLogins" open="(" close=")" item="userLogin" separator=",">#{userLogin}</foreach>
+        and u.login in <foreach collection="userLogins" open="(" close=")" item="userLogin" separator=",">#{userLogin,jdbcType=VARCHAR}</foreach>
       </if>
       <if test="query.searchQueryToSql != null">
-        and lower(u.name) like #{query.searchQueryToSql} ESCAPE '/'
+        and lower(u.name) like #{query.searchQueryToSql,jdbcType=VARCHAR} ESCAPE '/'
       </if>
       <!-- filter rows with user permissions -->
       <if test="query.withAtLeastOnePermission()">
@@ -41,10 +41,10 @@
           and ur.resource_id is null
         </if>
         <if test="query.componentUuid!=null">
-          and p.uuid = #{query.componentUuid}
+          and p.uuid = #{query.componentUuid,jdbcType=VARCHAR}
         </if>
         <if test="query.permission!=null">
-          and ur.role = #{query.permission}
+          and ur.role = #{query.permission,jdbcType=VARCHAR}
         </if>
       </if>
     </where>
diff --git a/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml
index b8cf8a717f0..1a75f8fc785 100644
--- a/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml
@@ -17,14 +17,14 @@
     select
     <include refid="groupColumns"/>
     from groups g
-    where g.organization_uuid = #{organizationUuid} and g.name = #{name}
+    where g.organization_uuid = #{organizationUuid,jdbcType=VARCHAR} and g.name = #{name,jdbcType=VARCHAR}
   </select>
 
   <select id="selectByKey" parameterType="string" resultType="Group">
     SELECT
     <include refid="groupColumns"/>
     FROM groups g
-    where g.name=#{id}
+    where g.name=#{id,jdbcType=BIGINT}
   </select>
 
   <select id="selectById" parameterType="long" resultType="Group">
@@ -32,14 +32,14 @@
     <include refid="groupColumns"/>
     FROM groups g
     <where>
-      g.id=#{id}
+      g.id=#{id,jdbcType=BIGINT}
     </where>
   </select>
 
   <delete id="deleteById" parameterType="long">
     DELETE FROM groups
     <where>
-      id=#{id}
+      id=#{id,jdbcType=BIGINT}
     </where>
   </delete>
 
@@ -49,7 +49,7 @@
     from groups g
     inner join groups_users gu on gu.group_id = g.id
     inner join users u on u.id = gu.user_id
-    where  u.login=#{login}
+    where  u.login=#{login,jdbcType=VARCHAR}
   </select>
 
   <select id="selectByNames" parameterType="map" resultType="Group">
@@ -93,9 +93,9 @@
     <include refid="groupColumns"/>
     from groups g
     where
-    organization_uuid = #{organizationUuid}
+    organization_uuid = #{organizationUuid,jdbcType=VARCHAR}
     <if test="query!=null">
-      and upper(g.name) like #{query} escape '/'
+      and upper(g.name) like #{query,jdbcType=VARCHAR} escape '/'
     </if>
     order by upper(g.name)
   </select>
@@ -104,9 +104,9 @@
     select count(1)
     from groups g
     where
-    organization_uuid = #{organizationUuid}
+    organization_uuid = #{organizationUuid,jdbcType=VARCHAR}
     <if test="query!=null">
-      and upper(g.name) like #{query} escape '/'
+      and upper(g.name) like #{query,jdbcType=VARCHAR} escape '/'
     </if>
   </select>
 
@@ -114,7 +114,7 @@
     select
     <include refid="groupColumns"/>
     from groups g
-    where g.organization_uuid = #{organizationUuid}
+    where g.organization_uuid = #{organizationUuid,jdbcType=VARCHAR}
     order by upper(g.name)
   </select>
 
diff --git a/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml
index 1c429bd8f0d..c7aaa37af75 100644
--- a/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml
@@ -15,13 +15,13 @@
 
   <delete id="delete" parameterType="map">
     delete from groups_users
-    where user_id = #{userId} and
-    group_id = #{groupId}
+    where user_id = #{userId,jdbcType=BIGINT} and
+    group_id = #{groupId,jdbcType=BIGINT}
   </delete>
 
   <delete id="deleteByGroupId" parameterType="long">
     delete from groups_users
-    where group_id = #{groupId}
+    where group_id = #{groupId,jdbcType=BIGINT}
   </delete>
 
 </mapper>
-- 
2.39.5