episkeyyy episkeyyy - 1 year ago 54
SQL Question

Using single quotes in a search bar

I'm trying to search for entries in my SQL database, but whenever I input a single quote in the search bar and run it, I get an error. When I type a specific name, it returns the right entries from the database. I've read about using '', and escaping, and using prepared statements but either none of the answers are the right fit for my case or I'm not understanding something conceptually. (I'm a beginner) Thanks in advance! :)

Below is the code for my jsp file, what it looks like when I run, and what happens when I insert a single quotation.

<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<!DOCTYPE html >
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>List of Entries</title>
<h1>Retrieve Data</h1>
<form method="post" action="index.jsp">
Search <input type="text" name="search"
placeholder="Enter your search here..." />
<input type="submit"
value="Search" name="submit"/>
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
ResultSet rs2 = null;

try {
String searchReq = request.getParameter("search");
try {
} catch (ClassNotFoundException e) {
//make connection to db
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/profile", "root", "");

//create a statement
stmt = con.createStatement();

//executes SQL query
rs = stmt.executeQuery("SELECT * FROM person WHERE Name LIKE '%"+ searchReq +"%'");

<table border="1">

try {
while (rs.next()) {


} catch (Exception e) {


catch (Exception e) {

finally {



jsp page with search bar

error page

Answer Source

your main problem is here:

"SELECT * FROM person WHERE Name LIKE '%"+ searchReq +"%'"

when variable searchReq contains single quote, you're creating sql query:

SELECT * FROM person WHERE Name LIKE '%'%'
                                       ^ sql engine consider this as end of string literal

when database tries to parse this query, it finds correct part SELECT * FROM person WHERE Name LIKE '%' and then invalid one %'

this is the same as in pure java, if you want to declare string with " inside, if you try:

String s = "my string with " quote";
                           ^ java considers this as end of string literal

you will get syntax error message, you need to escape such symbol, for java it is \"

so, to fix this, you need either use parametric queries, or check sql standard:

<character representation> ::= <nonquote character> | <quote symbol>

<quote symbol> ::= <quote><quote>

so, single quote should be present/escaped as double single quote ''

so, if you just replace all ' with '' - you will get rid of sql injections and fix failing query.

the simplest way is searchReq = searchReq.replaceAll("'", "''");

but, sql operator LIKE has 2 additional special symbols % and _

If an <escape character> is not specified, then each <underscore> character in P represents an arbitrary character specifier, each <percent> character in P represents an arbitrary string specifier, and each character in P that is neither the <underscore> character nor the <percent> character represents itself.

consider what will be found by your query if user inputs these symbols?

hint: take a look at standard:

<like predicate> ::= <match value> [ NOT ] LIKE <pattern> [ ESCAPE <escape character> ]