CODEWITHSUNDEEP
mysqlstored-procedures
Unexpected Pair of Colons
Unexpected Pair of Colons

Reputation: 769

Why does my stored procedure not recognize my varchar string?

I currently have a stored procedure in MySQL called Admin_Emails, which accepts three parameters: siteID VARCHAR(20) categoryID INT approved INT

The siteID is always in the following format: s-xxx(x)-xxxxxx.

In the procedure I add the siteID to my query using the following:

IF(siteID IS NOT NULL)
    THEN
        SET @query = CONCAT(@query, " AND e.site_id = ", siteID);
END IF;

If I run the procedure (ex. CALL Admin_Emails('s-osp-123ABC', 2, 1) then I get the following error:

#1054 - Unknown column 's' in 'where clause'

The s is coming from the siteID string. I tried modifying my CONCAT statement to the following:

SET @query = CONCAT(@query, " AND e.site_id = ", "'" + siteID + "'");

which will not produce an error, but will give me results with siteID's that do not match the input. I purposely produced an error by excluding the AND in the query, and it was showing a 0 where the siteID should be:

#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'e.site_id = 0 AND e.category_id = 2 AND e.is_approved != 0' at line 20

I'm at a loss. I've tried searches for issues related to VARCHAR strings in stored procedures, but can't find similar situations. Is anyone aware of what I'm doing wrong here?

I didn't want to overload with the whole procedure, so I'm hoping that what I've provided here is enough, but if not I can produce more of my procedure.

Upvotes: 1

Views: 454

Answers (1)

xQbert
xQbert

Reputation: 35333

why not 

CONCAT(@query, " AND e.site_id = ", "'", siteID,"'");

Seems odd to try and + when concat() supports n values of string concat...

I think ++ is trying to do math which is resulting in the error.

Or you have an or limit at the end of the query and with the addition of and.... if the or doesn't have proper ()'s it could cause the issue of unexpected results.

Be warned if SiteID passed in isn't properly sanitized, you have SQL injection possibilities now.

Upvotes: 2

Related Questions