Search MySQL with Multiple Tables and Multiple Search Termas
Hello
What is the Best way to search multiple tables in a MySQL database for Multiple search Terms?
Some Notes
In MSSQL I can use Named Parameters but these aren’t available in MySQL so I would have to pass 60 Parameters to the Prepared Statement.
This would have the Potential for Errors – Ensuring that each Parameter is in the Correct Order!!
How should I do this?
Following it the MSSQL code.
What is the Best way to search multiple tables in a MySQL database for Multiple search Terms?
Some Notes
- Currently I have it working Fine in MS SQL but have to change to MySQL
- The Search is done by Calling a Stored Procedure/Routine
- I am using Dynamic SQL to enable all the Search Filter Options
- I am also using Parameters to protect against SQL Injection.
- Search Columns are both Varchar and Integer
- The Procedure is sent 10 search Terms that need to be searched for.
In MSSQL I can use Named Parameters but these aren’t available in MySQL so I would have to pass 60 Parameters to the Prepared Statement.
This would have the Potential for Errors – Ensuring that each Parameter is in the Correct Order!!
How should I do this?
Following it the MSSQL code.
DECLARE @SQLSelectFields NVARCHAR(MAX) = ''
DECLARE @SQLString NVARCHAR(MAX) = ''
DECLARE @SQLFilter NVARCHAR(MAX) = ''
DECLARE @SQLTables NVARCHAR(MAX) = ''
-- Tables to Select From
SET @SQLTables = ' dbo.PURCHORD_HDR AS POH ' + ' INNER JOIN dbo.ppp_vw_Company C ON C.AccountID = POH.ACCNO AND C.AccountType = 2 '
+ ' LEFT OUTER JOIN dbo.CONTACTS CO ON CO.SEQNO = POH.CONTACT_SEQNO '
+ ' LEFT OUTER JOIN dbo.NARRATIVES N ON N.SEQNO = POH.NARRATIVE_SEQNO '
-- Min Fields for General Searching and use
SET @SQLSelectFields = ' POH.SEQNO, POH.STATUS, POH.ORDERDATE, POH.DUEDATE, POH.REFERENCE, POH.ADDRESS1, POH.ADDRESS2, POH.ADDRESS3, POH.ADDRESS4, POH.ADDRESS5,'
+ 'POH.ADDRESS6, POH.SUBTOTAL, POH.TAXTOTAL, POH.CURRENCYNO, POH.EXCHRATE, POH.LAST_UPDATED, POH.BRANCHNO, POH.ORDTOTAL, POH.LEADTIMEUSED, '
+ 'POH.NARRATIVE_SEQNO, N.NARRATIVE, POH.PROCESSFINALISATION, POH.CREATE_DATE, POH.DEFLOCNO, 0 AS ''PlacedStatus'', C.AccountID, C.CompanyName, C.Phone,'
+ 'C.Email, C.CompanyTypeName, C.PrimaryGroup, C.SecondaryGroup, C.BranchNo, C.StaffID, C.AccountCode, C.CurrencyID, C.CreditTermsID,'
+ 'C.TaxRateID, CO.SEQNO, CO.FIRSTNAME, CO.LASTNAME, CO.DIRECTPHONE, CO.MOBILE, CO.EMAIL, CO.ISACTIVE, C.PrimaryLeadTime, C.SecondaryLeadTime'
-- Add Search Filter Options
IF @IncludeUnConfirmed = 0
BEGIN
-- Include UnConfirmed Orders
SET @SQLFilter = @SQLFilter + ' AND POH.ISCONFIRMED <> ''N'' '
END
IF @IncludeConfirmed = 0
BEGIN
-- Include UnConfirmed Orders
SET @SQLFilter = @SQLFilter + ' AND POH.ISCONFIRMED <> ''Y'' '
END
-- Check if Filtering By Customer Account
IF @CompanyAccountNumber > 0
BEGIN
SET @SQLFilter = @SQLFilter + ' AND POH.ACCNO = @CompanyAccountNumber '
END
-- Trim and Add the % to the Search words
SET @SearchText1 = LTRIM(RTRIM(ISNULL(@SearchText1, '')))
SET @SearchText2 = LTRIM(RTRIM(ISNULL(@SearchText2, '')))
SET @SearchText3 = '%' + LTRIM(RTRIM(ISNULL(@SearchText3, ''))) + '%'
SET @SearchText4 = '%' + LTRIM(RTRIM(ISNULL(@SearchText4, ''))) + '%'
SET @SearchText5 = '%' + LTRIM(RTRIM(ISNULL(@SearchText5, ''))) + '%'
SET @SearchText6 = '%' + LTRIM(RTRIM(ISNULL(@SearchText6, ''))) + '%'
SET @SearchText7 = '%' + LTRIM(RTRIM(ISNULL(@SearchText7, ''))) + '%'
SET @SearchText8 = '%' + LTRIM(RTRIM(ISNULL(@SearchText8, ''))) + '%'
SET @SearchText9 = '%' + LTRIM(RTRIM(ISNULL(@SearchText9, ''))) + '%'
SET @SearchText10 = '%' + LTRIM(RTRIM(ISNULL(@SearchText10, ''))) + '%'
SET @SQLString = 'SELECT DISTINCT ' + @SQLSelectFields + ' FROM ' + @SQLTables
+ 'WHERE (C.CompanyName LIKE @SearchText1 OR C.AccountID LIKE @SearchText1 OR C.AccountCode LIKE @SearchText1 OR POH.REFERENCE LIKE @SearchText1 OR C.CompanyID LIKE @SearchText1) AND '
+ '(C.CompanyName LIKE @Searchtext2 OR C.AccountID LIKE @Searchtext2 OR C.AccountCode LIKE @Searchtext2 OR POH.REFERENCE LIKE @Searchtext2 OR C.CompanyID LIKE @Searchtext2) AND '
+ '(C.CompanyName LIKE @Searchtext3 OR C.AccountID LIKE @Searchtext3 OR C.AccountCode LIKE @Searchtext3 OR POH.REFERENCE LIKE @Searchtext3 OR C.CompanyID LIKE @Searchtext3) AND '
+ '(C.CompanyName LIKE @Searchtext4 OR C.AccountID LIKE @Searchtext4 OR C.AccountCode LIKE @Searchtext4 OR POH.REFERENCE LIKE @Searchtext4 OR C.CompanyID LIKE @Searchtext4) AND '
+ '(C.CompanyName LIKE @Searchtext5 OR C.AccountID LIKE @Searchtext5 OR C.AccountCode LIKE @Searchtext5 OR POH.REFERENCE LIKE @Searchtext5 OR C.CompanyID LIKE @Searchtext5) AND '
+ '(C.CompanyName LIKE @Searchtext6 OR C.AccountID LIKE @Searchtext6 OR C.AccountCode LIKE @Searchtext6 OR POH.REFERENCE LIKE @Searchtext6 OR C.CompanyID LIKE @Searchtext6) AND '
+ '(C.CompanyName LIKE @Searchtext7 OR C.AccountID LIKE @Searchtext7 OR C.AccountCode LIKE @Searchtext7 OR POH.REFERENCE LIKE @Searchtext7 OR C.CompanyID LIKE @Searchtext7) AND '
+ '(C.CompanyName LIKE @Searchtext8 OR C.AccountID LIKE @Searchtext8 OR C.AccountCode LIKE @Searchtext8 OR POH.REFERENCE LIKE @Searchtext8 OR C.CompanyID LIKE @Searchtext8) AND '
+ '(C.CompanyName LIKE @Searchtext9 OR C.AccountID LIKE @Searchtext9 OR C.AccountCode LIKE @Searchtext9 OR POH.REFERENCE LIKE @Searchtext9 OR C.CompanyID LIKE @Searchtext9) AND '
+ '(C.CompanyName LIKE @Searchtext10 OR C.AccountID LIKE @Searchtext10 OR C.AccountCode LIKE @Searchtext10 OR POH.REFERENCE LIKE @Searchtext10 OR C.CompanyID LIKE @Searchtext10)'
+ @SQLFilter
PRINT @SQLString
EXECUTE sp_executesql
@SQLString,
N'@SearchText1 NVARCHAR(50), @SearchText2 NVARCHAR(50), @SearchText3 NVARCHAR(50), @SearchText4 NVARCHAR(50), @SearchText5 NVARCHAR(50), @SearchText6 NVARCHAR(50), @SearchText7 NVARCHAR(50), @SearchText8 NVARCHAR(50), @SearchText9 NVARCHAR(50), @SearchText10 NVARCHAR(50), @OrderStatusTable IntegerTableType ReadOnly,@CompanyAccountNumber integer',
@SearchText1, @SearchText2, @SearchText3, @SearchText4, @SearchText5, @SearchText6, @SearchText7, @SearchText8, @SearchText9,
@SearchText10, @OrderStatusTable, @CompanyAccountNumber
RETURN;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Olaf
Does sending a plain String created in PHP open the door to SQL Injection?
That was one reason the MSSQL was coded with Stored Procedures and Parameterized SQL.
Thanks Again
Ron
Does sending a plain String created in PHP open the door to SQL Injection?
That was one reason the MSSQL was coded with Stored Procedures and Parameterized SQL.
Thanks Again
Ron
Well, that's the reason you use parameterized queries in MySQL, too.
But putting together the parameterized query doesn't need to be done in a stored procedure to be safe. What does it differ if that part is done on the outside or inside? Doing this in a sproc with only parameters passed in is better encapsulation, but MySQL doesn't have sp_executesql.
Queries that are put together from literals and no user input are the same, as if you programmed them yourself.
Every operation you do in your MSSQL code is keeping user input in a variable you don't concatenate into your query and that's fine, you change the user input by adding wildcard % for usage in LIKE, that's ok since the final query still has the parameter variable names in it and you pass them on to sp_executesql.
The same has to be kept in the PHP code. the sp_executesql just becomes PHPs $stmt->execute(); and beforehand you prepare and bindparam, which is just PHPs mechanism of passing in SQL and parameter variables separately. MySQL then executes a parameterized query, it only executes an unsafe query string with concatenated user input, if you program that way.
Bye, Olaf.
But putting together the parameterized query doesn't need to be done in a stored procedure to be safe. What does it differ if that part is done on the outside or inside? Doing this in a sproc with only parameters passed in is better encapsulation, but MySQL doesn't have sp_executesql.
Queries that are put together from literals and no user input are the same, as if you programmed them yourself.
Every operation you do in your MSSQL code is keeping user input in a variable you don't concatenate into your query and that's fine, you change the user input by adding wildcard % for usage in LIKE, that's ok since the final query still has the parameter variable names in it and you pass them on to sp_executesql.
The same has to be kept in the PHP code. the sp_executesql just becomes PHPs $stmt->execute(); and beforehand you prepare and bindparam, which is just PHPs mechanism of passing in SQL and parameter variables separately. MySQL then executes a parameterized query, it only executes an unsafe query string with concatenated user input, if you program that way.
Bye, Olaf.
Now that I get noted again, let me add while MySQL has no such stored proc the concepts of PHP's prepare and execute do have the corresponding ends in MySQL, you have PREPARE and EXECUTE and PHPs mechanisms make use of exactly these. But that doesn't change anything about the core vulnerability being to put together strings without sanitizing user input, i.e. a MySQL stored proc putting together SQL the insecure way can also "harm itself" with EXECUTE, that's not the topic of which layer does it.
If you go as far as considering the PHP layer as potentially compromitted while the database is not, then you'd perhaps like to even only allow PHP to call stored procs and functions and nothing else and define procedure security to not act as invoker but definer, but typically PHP code will connect with credentials able to do more, if a hacker is in the server and can execute PHP he'll be in the MySQL DB, too. So you can see PHP as an extension to MySQL for code that can use a richer language for sanitizing input and do so much more than MySQL offers, I always find its own language lacking and only force myself to core MySQL procedures when going as deep as triggers you definitely need to implement in the DB.
Bye, Olaf.
If you go as far as considering the PHP layer as potentially compromitted while the database is not, then you'd perhaps like to even only allow PHP to call stored procs and functions and nothing else and define procedure security to not act as invoker but definer, but typically PHP code will connect with credentials able to do more, if a hacker is in the server and can execute PHP he'll be in the MySQL DB, too. So you can see PHP as an extension to MySQL for code that can use a richer language for sanitizing input and do so much more than MySQL offers, I always find its own language lacking and only force myself to core MySQL procedures when going as deep as triggers you definitely need to implement in the DB.
Bye, Olaf.
You may fork the git and add the additional search conditions.