1
2
3
4
5
6
7
8
9
10
11 package psiprobe.controllers.sql;
12
13 import static org.junit.jupiter.api.Assertions.assertEquals;
14 import static org.junit.jupiter.api.Assertions.assertNull;
15 import static org.junit.jupiter.api.Assertions.assertTrue;
16 import static org.mockito.Mockito.mock;
17 import static org.mockito.Mockito.when;
18
19 import java.sql.Connection;
20 import java.sql.PreparedStatement;
21 import java.sql.ResultSet;
22 import java.sql.ResultSetMetaData;
23 import java.sql.SQLException;
24 import java.util.List;
25 import java.util.Locale;
26 import java.util.Map;
27
28 import javax.naming.NamingException;
29 import javax.sql.DataSource;
30
31 import org.apache.catalina.Context;
32 import org.junit.jupiter.api.BeforeEach;
33 import org.junit.jupiter.api.Test;
34 import org.springframework.context.support.StaticApplicationContext;
35 import org.springframework.mock.web.MockHttpServletRequest;
36 import org.springframework.mock.web.MockHttpServletResponse;
37 import org.springframework.test.util.ReflectionTestUtils;
38 import org.springframework.web.servlet.ModelAndView;
39
40 import psiprobe.TomcatContainer;
41 import psiprobe.beans.ContainerWrapperBean;
42 import psiprobe.beans.ResourceResolver;
43 import psiprobe.model.sql.DataSourceTestInfo;
44
45 class ExecuteSqlControllerTest {
46
47 private ExecuteSqlController controller;
48
49 private ContainerWrapperBean containerWrapper;
50
51 private ResourceResolver resourceResolver;
52
53 private TomcatContainer tomcatContainer;
54
55 private Context context;
56
57 @BeforeEach
58 void setUp() throws Exception {
59 controller = new ExecuteSqlController();
60 controller.setViewName("ajax/sql/recordset");
61
62 StaticApplicationContext applicationContext = new StaticApplicationContext();
63 applicationContext.addMessage("probe.src.dataSourceTest.sql.required", Locale.getDefault(),
64 "sql required");
65 applicationContext.addMessage("probe.src.dataSourceTest.resource.lookup.failure",
66 Locale.getDefault(), "lookup {0}");
67 applicationContext.addMessage("probe.src.dataSourceTest.sql.failure", Locale.getDefault(),
68 "sql failure {0}");
69 applicationContext.addMessage("probe.src.dataSourceTest.sql.null", Locale.getDefault(), "NULL");
70 applicationContext.refresh();
71 controller.setApplicationContext(applicationContext);
72
73 containerWrapper = mock(ContainerWrapperBean.class);
74 resourceResolver = mock(ResourceResolver.class);
75 tomcatContainer = mock(TomcatContainer.class);
76 context = mock(Context.class);
77
78 when(containerWrapper.getResourceResolver()).thenReturn(resourceResolver);
79 when(containerWrapper.getTomcatContainer()).thenReturn(tomcatContainer);
80 when(tomcatContainer.formatContextName("app")).thenReturn("/app");
81 when(tomcatContainer.findContext("/app")).thenReturn(context);
82 ReflectionTestUtils.setField(controller, "containerWrapper", containerWrapper);
83 }
84
85 @Test
86 void handleRequestReturnsEscapedResultSetAndStoresSessionState() throws Exception {
87 DataSource dataSource = mock(DataSource.class);
88 Connection connection = mock(Connection.class);
89 PreparedStatement statement = mock(PreparedStatement.class);
90 ResultSet resultSet = mock(ResultSet.class);
91 ResultSetMetaData metaData = mock(ResultSetMetaData.class);
92
93 when(resourceResolver.lookupDataSource(context, "jdbc/test", containerWrapper))
94 .thenReturn(dataSource);
95 when(dataSource.getConnection()).thenReturn(connection);
96 when(connection.prepareStatement("select * from t")).thenReturn(statement);
97 when(statement.execute()).thenReturn(true);
98 when(statement.getResultSet()).thenReturn(resultSet);
99 when(resultSet.getMetaData()).thenReturn(metaData);
100 when(metaData.getColumnCount()).thenReturn(2);
101 when(metaData.getColumnLabel(1)).thenReturn("name");
102 when(metaData.getColumnLabel(2)).thenReturn("name");
103 when(resultSet.next()).thenReturn(true, true);
104 when(resultSet.getString(1)).thenReturn("<b>unsafe</b>");
105 when(resultSet.getString(2)).thenReturn((String) null);
106 when(resultSet.wasNull()).thenReturn(false, true);
107
108 MockHttpServletRequest request = new MockHttpServletRequest("GET", "/sql/recordset.ajax");
109 request.addParameter("webapp", "app");
110 request.addParameter("resource", "jdbc/test");
111 request.addParameter("sql", "select * from t");
112 request.addParameter("maxRows", "1");
113 request.addParameter("rowsPerPage", "20");
114 request.addParameter("historySize", "5");
115 request.getSession(true);
116
117 ModelAndView modelAndView = controller.handleRequest(request, new MockHttpServletResponse());
118
119 assertEquals("ajax/sql/recordset", modelAndView.getViewName());
120 assertEquals("1", modelAndView.getModel().get("rowsAffected"));
121 assertEquals("20", modelAndView.getModel().get("rowsPerPage"));
122
123 @SuppressWarnings("unchecked")
124 List<Map<String, String>> results =
125 (List<Map<String, String>>) modelAndView.getModel().get("results");
126 assertEquals(1, results.size());
127 assertEquals("<b>unsafe</b>", results.get(0).get("name"));
128 assertEquals("NULL", results.get(0).get("name "));
129
130 DataSourceTestInfo sessionData = (DataSourceTestInfo) request.getSession(false)
131 .getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR);
132 assertEquals(1, sessionData.getResults().size());
133 assertEquals(List.of("select * from t"), sessionData.getQueryHistory());
134 assertEquals(1, sessionData.getMaxRows());
135 assertEquals(20, sessionData.getRowsPerPage());
136 assertEquals(5, sessionData.getHistorySize());
137 }
138
139 @Test
140 void handleRequestReturnsUpdateCountWhenStatementHasNoResultSet() throws Exception {
141 DataSource dataSource = mock(DataSource.class);
142 Connection connection = mock(Connection.class);
143 PreparedStatement statement = mock(PreparedStatement.class);
144
145 when(resourceResolver.lookupDataSource(context, "jdbc/test", containerWrapper))
146 .thenReturn(dataSource);
147 when(dataSource.getConnection()).thenReturn(connection);
148 when(connection.prepareStatement("update t set c = 1")).thenReturn(statement);
149 when(statement.execute()).thenReturn(false);
150 when(statement.getUpdateCount()).thenReturn(7);
151
152 MockHttpServletRequest request = new MockHttpServletRequest("GET", "/sql/recordset.ajax");
153 request.addParameter("webapp", "app");
154 request.addParameter("resource", "jdbc/test");
155 request.addParameter("sql", "update t set c = 1");
156 request.getSession(true);
157
158 ModelAndView modelAndView = controller.handleRequest(request, new MockHttpServletResponse());
159
160 assertEquals("7", modelAndView.getModel().get("rowsAffected"));
161 assertEquals("0", modelAndView.getModel().get("rowsPerPage"));
162 assertTrue(modelAndView.getModel().containsKey("results"));
163 assertNull(modelAndView.getModel().get("results"));
164 }
165
166 @Test
167 void handleRequestSetsErrorMessagesForBlankSqlLookupFailureAndSqlException() throws Exception {
168 MockHttpServletRequest blankSqlRequest =
169 new MockHttpServletRequest("GET", "/sql/recordset.ajax");
170 blankSqlRequest.addParameter("webapp", "app");
171 blankSqlRequest.addParameter("sql", " ");
172 blankSqlRequest.getSession(true);
173 ModelAndView blankSql =
174 controller.handleRequest(blankSqlRequest, new MockHttpServletResponse());
175 assertEquals("ajax/sql/recordset", blankSql.getViewName());
176 assertEquals("sql required", blankSqlRequest.getAttribute("errorMessage"));
177
178 MockHttpServletRequest lookupFailureRequest =
179 new MockHttpServletRequest("GET", "/sql/recordset.ajax");
180 lookupFailureRequest.addParameter("webapp", "app");
181 lookupFailureRequest.addParameter("resource", "jdbc/missing");
182 lookupFailureRequest.addParameter("sql", "select 1");
183 lookupFailureRequest.getSession(true);
184 when(resourceResolver.lookupDataSource(context, "jdbc/missing", containerWrapper))
185 .thenThrow(new NamingException("boom"));
186 controller.handleRequest(lookupFailureRequest, new MockHttpServletResponse());
187 assertEquals("lookup jdbc/missing", lookupFailureRequest.getAttribute("errorMessage"));
188
189 DataSource dataSource = mock(DataSource.class);
190 when(resourceResolver.lookupDataSource(context, "jdbc/test", containerWrapper))
191 .thenReturn(dataSource);
192 when(dataSource.getConnection()).thenThrow(new SQLException("broken"));
193
194 MockHttpServletRequest sqlFailureRequest =
195 new MockHttpServletRequest("GET", "/sql/recordset.ajax");
196 sqlFailureRequest.addParameter("webapp", "app");
197 sqlFailureRequest.addParameter("resource", "jdbc/test");
198 sqlFailureRequest.addParameter("sql", "select 1");
199 sqlFailureRequest.getSession(true);
200
201 ModelAndView sqlFailure =
202 controller.handleRequest(sqlFailureRequest, new MockHttpServletResponse());
203
204 assertEquals("ajax/sql/recordset", sqlFailure.getViewName());
205 assertEquals("sql failure broken", sqlFailureRequest.getAttribute("errorMessage"));
206 }
207 }