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

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;

Select allOpen in new window

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

Select allOpen in new window

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, "%' ");

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.

Sorry I don't understand what I need to do to my code.

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.

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;

The code, as written, will not generate valid SQL.  It looks like something is missing.  Is there any other code applicable to this?

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;

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


Also I put
END    - unexpected end

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.

Same error...
Don't understand

needed

DELIMITER $$