• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

How to escape characters so any characters can be passed to a stored procedure

Hi..
I have the following SP. How can allow any char to be passed without breaking the code and making the SQL invalid?



CREATE  PROCEDURE `sproc_Search_All`(IN inQ varchar(100))
BEGIN

DECLARE qs VARCHAR(5000);

set inQ=replace(inQ,'''','''''');


set @qs = "SELECT *  FROM CUSTOMERS "

set @qs = concat(@qs," AND ( ", "NAME  like '%", inQ, "%' ");

set @qs = concat(@qs,") ORDER BY NAME ");


PREPARE preparedstatement FROM @qs ;

EXECUTE preparedstatement;

DEALLOCATE PREPARE preparedstatement;


END
0
JElster
Asked:
JElster
  • 7
  • 7
1 Solution
 
Steve BinkCommented:
Please see the MySQL docs on how to parameterize prepared SQL.
The first example shows how to create a prepared statement by using 
a string literal to supply the text of the statement:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

Open in new window

0
 
Steve BinkCommented:
To be clear, the EXECUTE..USING statement will properly escape the contents of any variables being used.  A better example for your use case would look like this:
CREATE  PROCEDURE `sproc_Search_All`(IN inQ varchar(100))
  BEGIN
    PREPARE stmt FROM 'SELECT *  FROM CUSTOMERS WHERE NAME LIKE ?';
    SET @qs = CONCAT('%', inQ, '%');
    EXECUTE stmt USING @qs;
    DEALLOCATE PREPARE stmt;
  END

Open in new window

0
 
JElsterAuthor Commented:
What about in the case where I build an expression like



set @qs = concat(@qs," AND ( ", "LOWER(NAME)  like '%", inQ, "%' ");

set @qs = concat(@qs," OR ", "LOWER(TITLE)  like '%", inQ, "%' ");
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Steve BinkCommented:
Did you read the docs found at the link I posted?  That page explains how PREPARE/EXECUTE can be used.  The first code example I posted demonstrates how to use multiple parameters.

Set your statement to the entire, built SQL.  Anywhere you need a parameter, place a question mark.  When you call EXECUTE..USING, provide enough values to fill each parameter.
0
 
JElsterAuthor Commented:
Sorry I don't understand what I need to do to my code.
0
 
Steve BinkCommented:
Provide an example of the full SQL you want to run.  Be sure to note which portions of the SQL are to be replaced by user-supplied content.
0
 
JElsterAuthor Commented:
CREATE  PROCEDURE `sproc_Search_All`(IN inQ varchar(100))
 BEGIN

 DECLARE qs VARCHAR(5000);

 set inQ=replace(inQ,'''','''''');


 set @qs = "SELECT *  FROM CUSTOMERS "

 set @qs = concat(@qs," AND ( ", "NAME  like '%", inQ, "%' ");

 set @qs = concat(@qs,") ORDER BY NAME ");


 PREPARE preparedstatement FROM @qs ;

 EXECUTE preparedstatement;

 DEALLOCATE PREPARE preparedstatement;
0
 
Steve BinkCommented:
The code, as written, will not generate valid SQL.  It looks like something is missing.  Is there any other code applicable to this?
0
 
JElsterAuthor Commented:
Here's the orginal.. thx

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_Search_All`(IN inQ varchar(100))
BEGIN

DECLARE qs VARCHAR(5000);

set inQ=replace(inQ,'''','''''');


set @qs = "SELECT DISTINCT PCI.Id, PCI.ISBN, IFNULL(DT.MilleniumTitle, PCI.Title), PCI.Type, dt.Priority,
 DATEDIFF(Now(),PCI.CreatedDate), DATEDIFF(Now(),A.CreatedDate), DT.PubMonth, A.CurrentQueue,
concat(A.CurrentStatus, ' / ', A.CurrentStatus2), concat(U.First_Name, ' ',
substr(U.Last_Name,1,1)) FLN, Trim(A.CurrentPercent), concat(SUBSTRING(DT.TradeOrOP,1,10),'...'),
A.CurrentQueue2, DT.ExistingTitleId, concat(U2.First_Name, ' ', substr(U2.Last_Name,1,1)) FLN2,
 TRIM(A.CurrentPercent2), dt.noofquestions, dt.author, dt.pagecount
FROM PreConversionInfo PCI
LEFT OUTER JOIN Assignments A ON A.TitleId=PCI.Id
LEFT OUTER JOIN Users U on A.AssignedTo1=U.Id
LEFT OUTER JOIN Users U2 on A.AssignedTo2=U2.Id
LEFT OUTER JOIN detailedtitleinfo dt ON pci.id = dt.titleid
LEFT OUTER JOIN dropdownvalues d ON dt.Priority = d.DisplayValue
LEFT OUTER JOIN OptionalSteps O ON O.titleid = PCI.Id
WHERE PCI.Available='Yes' and A.CurrentQueue is not null and (A.Closed = '' or A.Closed is null)";


set @qs = concat(@qs," AND ( ", "LOWER(DT.MilleniumTitle)  like '%", inQ, "%' ");

set @qs = concat(@qs," OR ", "LOWER(PCI.ISBN)  like '%", inQ, "%' ");


set @qs = concat(@qs,") ORDER BY d.OrderValue DESC ");

/*
call debug_msg(TRUE,@qs);
*/

PREPARE preparedstatement FROM @qs ;

EXECUTE preparedstatement;

DEALLOCATE PREPARE preparedstatement;
0
 
Steve BinkCommented:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_Search_All`(IN inQ varchar(100))
BEGIN

SET @qs=CONCAT('%', inQ, '%');

SET @sql = CONCAT(
  "SELECT DISTINCT ",
    "PCI.Id, PCI.ISBN, IFNULL(DT.MilleniumTitle, PCI.Title), PCI.Type, dt.Priority, ",
    "DATEDIFF(Now(),PCI.CreatedDate), DATEDIFF(Now(),A.CreatedDate), DT.PubMonth, ",
    "A.CurrentQueue, concat(A.CurrentStatus, ' / ', A.CurrentStatus2), ",
    "concat(U.First_Name, ' ', substr(U.Last_Name,1,1)) FLN, Trim(A.CurrentPercent), ",
    "concat(SUBSTRING(DT.TradeOrOP,1,10),'...'), A.CurrentQueue2, DT.ExistingTitleId, ",
    "concat(U2.First_Name, ' ', substr(U2.Last_Name,1,1)) FLN2, TRIM(A.CurrentPercent2), ",
    "dt.noofquestions, dt.author, dt.pagecount ",
  "FROM PreConversionInfo PCI ",
    "LEFT OUTER JOIN Assignments A ON A.TitleId=PCI.Id ",
    "LEFT OUTER JOIN Users U on A.AssignedTo1=U.Id ",
    "LEFT OUTER JOIN Users U2 on A.AssignedTo2=U2.Id ",
    "LEFT OUTER JOIN detailedtitleinfo dt ON pci.id = dt.titleid ",
    "LEFT OUTER JOIN dropdownvalues d ON dt.Priority = d.DisplayValue ",
    "LEFT OUTER JOIN OptionalSteps O ON O.titleid = PCI.Id ",
  "WHERE ",
    "PCI.Available='Yes' ",
    "and A.CurrentQueue is not null ",
    "and (A.Closed = '' or A.Closed is null) ",
    "AND ( LOWER(DT.MilleniumTitle)  like ?  OR LOWER(PCI.ISBN)  like ? ) ",
  "ORDER BY d.OrderValue DESC"
  );

PREPARE stmt FROM @sql;
EXECUTE stmt USING @qs, @qs;
DEALLOCATE PREPARE stmt;

Open in new window

0
 
JElsterAuthor Commented:
THX..
Getting syntax error
SET @qs=CONCAT('%', inQ, '%');      Expecting ;


Also I put
END    - unexpected end
0
 
Steve BinkCommented:
You do need an "END;" at the end of the procedure.  I cannot reproduce the syntax error you are receiving.  The SQL construction works fine on my test environment, even without the tables.  Try manually retyping the line, in case your cut/paste carried any artifacts with it.
0
 
JElsterAuthor Commented:
Same error...
Don't understand
0
 
JElsterAuthor Commented:
needed

DELIMITER $$
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now