1

Below are two stored procedures for MySQL community server version 8.0.11. I have seen some stored procedures written the first way using the back-quote: `, character in the parameter list and query statement. However, I have also seen queries written like the second one where there are no back-quote characters.

Which way is the best practice to follow? Are they any security differences? If one uses the back-quote character in stored procedures are they more vulnerable to SQL injection attacks? 

CREATE PROCEDURE `procedure`(IN `in_data` VARCHAR(100))
BEGIN
    SELECT COUNT(*) FROM `table_name` WHERE `data` = `in_data`;
END

Or:

CREATE PROCEDURE `procedure`(IN in_data VARCHAR(100))
BEGIN
    SELECT COUNT(*) FROM table_name WHERE data = in_data;
END

I hope I made this clear enough, thank you for your time.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Dillon
  • 1,394
  • 1
  • 11
  • 25

1 Answers1

2

As long as your procedure is not doing dynamic SQL with PREPARE and EXECUTE, the queries are fixed and they cannot be vulnerable to SQL injection.

The back-ticks are meant to delimit identifiers (table names, column names, procedure names, etc.) to allow you to use an identifier that wouldn't be legal because they contain punctuation or white space or international characters or conflict with SQL reserved words.

For examples, see my answer to this question: Do different databases use different name quote?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks Bill, so if one called the stored procedure using Java like `CallableStatement cs = connection.prepareCall("CALL procedure(?)"); cs.setString(1, var);` Where var is some user input. They would not be at risk to SQL injections? Or would one must sanitize the input, var, as well? – Dillon Jul 04 '18 at 17:17
  • That will not be at risk for SQL injections. – Bill Karwin Jul 04 '18 at 21:49