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
LVL 1
JElsterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.