Can I use the LIKE operator in a SQL query without worrying about SQL Injection vulnerabilities?

Question

Can I use the LIKE operator in a SQL query without worrying about SQL Injection vulnerabilities?

When dealing with POST data (json) in my express app, I have an endpoint that allows me to query a MySQL database safely. However, I am concerned about potential vulnerabilities when escaping and altering strings. Is there a risk of exploitation?

The goal is to modify the incoming string by inserting a '%' before the closing single quote in the LIKE clause.

var searchTerm = mysql.escape(req.body.firstName)
var newStr = "'" + searchTerm.substring(1, searchTerm.length - 1) + "%'"

// Example:  'alex' => 'alex%'

This method works fine, but I am curious if there is a more secure or recommended way to achieve this task.

Current query being used:

SELECT * FROM tbl WHERE col LIKE " + newStr + " ORDER BY key

For those seeking reference:

I am utilizing the following package: https://github.com/felixge/node-mysql

UPDATE/ANSWER

In response to EternalHour's suggestion, a more appropriate syntax would be:

var newStr = req.body.firstName + "%"
var sql = "SELECT cols FROM tbl WHERE col LIKE ? ORDER BY key"
sql = mysql.format(sql, newStr)

javascript mysql sql express node-mysql

Answer 1

Answer №1

In accordance with Christian's suggestion (but in a different programming language), it is advised to utilize prepared statements for enhanced security. It appears that you will need to modify your string to eliminate the single quotes, as prepared statements usually handle escaping automatically. This particular library seems to follow the same protocol.

Although unfamiliar with this specific library, it would likely look something like this:

var query = "SELECT * FROM tbl WHERE col LIKE ? ORDER BY key";
var newStr = searchTerm.substring(1, searchTerm.length - 1) + "%";
sql = mysql.format(query, newStr);

Additionally, it is recommended not to use * in SQL select queries.

Answer 2

In accordance with Christian's suggestion (but in a different programming language), it is advised to utilize prepared statements for enhanced security. It appears that you will need to modify your string to eliminate the single quotes, as prepared statements usually handle escaping automatically. This particular library seems to follow the same protocol.

Although unfamiliar with this specific library, it would likely look something like this:

var query = "SELECT * FROM tbl WHERE col LIKE ? ORDER BY key";
var newStr = searchTerm.substring(1, searchTerm.length - 1) + "%";
sql = mysql.format(query, newStr);

Additionally, it is recommended not to use * in SQL select queries.

Answer 3

Answer №2

Test out the fresh newStr directly by using the code snippet below (avoid adding % explicitly) :

SELECT * FROM tbl WHERE col LIKE '" + newStr + "%' ORDER BY key

Answer 4

Test out the fresh newStr directly by using the code snippet below (avoid adding % explicitly) :

SELECT * FROM tbl WHERE col LIKE '" + newStr + "%' ORDER BY key

Answer 5

Answer №3

It is recommended to use a Prepared Statement for this task:

In C#:

//Creating a new SQL Command with the parameter @newStr
var command = new SqlCommand( "SELECT * FROM tbl WHERE col LIKE '@newStr' ORDER BY key", db);

//Setting the parameter @newStr to 40 characters
command.Parameters.Add("@newStr", SqlDbType.VarChar, 40);

//Preparing the statement after defining all parameters
command.Prepare();

//Setting Parameter @newStr to "newStrValue"
command.Parameters["@newStr"] = "newStrValue";

//Executing the Command
command.Execute();

For Node.js, there is a Package that supports prepared Statements:

https://www.npmjs.com/package/mysql2

By using Prepared Statements, you can prevent SQL Injection attacks as the Database recognizes user input and not as a direct SQL Command.

Answer 6

It is recommended to use a Prepared Statement for this task:

In C#:

//Creating a new SQL Command with the parameter @newStr
var command = new SqlCommand( "SELECT * FROM tbl WHERE col LIKE '@newStr' ORDER BY key", db);

//Setting the parameter @newStr to 40 characters
command.Parameters.Add("@newStr", SqlDbType.VarChar, 40);

//Preparing the statement after defining all parameters
command.Prepare();

//Setting Parameter @newStr to "newStrValue"
command.Parameters["@newStr"] = "newStrValue";

//Executing the Command
command.Execute();

For Node.js, there is a Package that supports prepared Statements:

https://www.npmjs.com/package/mysql2

By using Prepared Statements, you can prevent SQL Injection attacks as the Database recognizes user input and not as a direct SQL Command.

Can I use the LIKE operator in a SQL query without worrying about SQL Injection vulnerabilities?

Answer №1

Answer №2

Answer №3

Similar questions

Adjust background dimensions using jQuery

JavaScript closing of a window

What is the best way to retrieve the maximum row number within each group or partition in SQL Server?

Tips for updating a JSON object value in Node.js

In a Ruby on Rails application, utilize jQuery to display an alert when a user does not accept the

JSP page displaying a table with a text input field named "code" enclosed within <TD> tags

Is there a way to achieve this without relying on Ext.getCmp in this particular code snippet?

Experiencing difficulties with setting up SearchApp

send data to a Node.js application using the POST method

AngularJS Toggle Directive tutorial: Building a toggle directive in Angular

Formatting dates in PHP with a mySQL query

My SQL query has been optimized to be 40% more efficient, yet it now runs 60 times slower

The showdown between AngularJS Directive Restrict A and E

transmit JSON formatted form data to an AngularJS platform

Utilizing Athena to efficiently query map-type columns with dynamically changing key names

Sending form data to the server using JavaScript: A step-by-step guide

Counter cannot be accessed once it has been updated

Difficulty commencing a background operation in a node.js application

Utilizing JavaScript to target a specific div element and modify the href attribute

What do you want to know about Angular JS $http request?