php search mysql index by phrase

Question

Have seen several examples of where a full text search by phrase works when the words are enclosed in double quotes. However, in my case I have a variable coming from the post array that I want to search on.

function find_images($term,$dbh) {
$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( "$term" IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");
$results->execute();

This does not work:

Parse error: syntax error, unexpected '$term' (T_VARIABLE) in ...

I was using

AGAINST('+$term*' IN BOOLEAN MODE) ...

but that returns results that contain any word in the phrase. If I type the exact phrase into the code instead, I do get the correct results. So my question is how do I put my variable into the double quotes in my code w/o throwing an error?

Assuming `$dbh` is a `mysqli` or `pdo` object - you'd be better binding the string as a parameter anyway, something like `AGAINST(? IN BOOLEAN MODE)` for a mysqli placeholder. — CD001, Oct 12 '17 at 15:34
If an answer solved your problem, consider accepting the answer. Here's how http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work then return here and do the same with the tick/checkmark till it turns green. This informs the community, a solution was found. Otherwise, others may think the question is still open and may want to post (more) answers. You'll earn points and others will be encouraged to help you. *Welcome to Stack!* — Jay Blanchard, Oct 12 '17 at 15:47

score 4 · Answer 1 · answered Oct 12 '17 at 15:33

4

Your quotes are all wrong. PHP will interpolate variables in single quotes in a query like this:

function find_images($term,$dbh) {
$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( '$term' IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");
$results->execute();

Warning!

Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe!

Since you're already using a database API which supports them, and you're preparing the query, it would benefit you to use prepared statements with placeholders for variables.

answered Oct 12 '17 at 15:33

Jay Blanchard

34,243
16
77
119

4

I wonder why OP is using prepare but not actually preparing any of the variables.. – GrumpyCrouton Oct 12 '17 at 15:35
I was curious about that too @GrumpyCrouton – Jay Blanchard Oct 12 '17 at 15:38
@GrumpyCrouton: If the developer is junior enough not to be able to troubleshoot string concatenation, then understanding the importance of query parameters is too advanced for them right now. – Bill Karwin Oct 12 '17 at 15:54
Hey @BillKarwin - side note: I have your book. It is excellent and we use it with newer database folks all the time. – Jay Blanchard Oct 12 '17 at 16:01
@JayBlanchard, thanks! I'm glad it is helpful. – Bill Karwin Oct 12 '17 at 16:01
@GrumpyCrouton are you here? – Jay Blanchard Oct 13 '17 at 11:50
@JayBlanchard Did you receive my email? – GrumpyCrouton Oct 13 '17 at 13:01
1

@JayBlanchard You're totally fine. Just wasn't sure if it sent, I don't really use my email from that domain very often. – GrumpyCrouton Oct 13 '17 at 13:05
Got it @GrumpyCrouton will read and review as soon as I can – Jay Blanchard Oct 13 '17 at 13:18

Bill Karwin · Answer 2 · 2017-10-12T16:01:06.603

This is not a SQL issue, it's a PHP issue. So let's simplify the problem and make it just a PHP string with a variable in it.

$string = "blah blah ("$term") yadda yadda";

Using PHP variables in string cannot be done this way, because the double quote that you want to use around your variable terminates the double quote at the start of the string.

You can make the double-quotes into literal double-quote characters instead of PHP string delimiters by escaping them:

$string = "blah blah (\"$term\") yadda yadda";

But I find that ugly.

You can also use different single-quotes inside the PHP string without escaping them:

$string = "blah blah ('$term') yadda yadda";

Because single-quotes inside double-quotes are treated as literal characters. In the case of SQL, it's preferable because single-quotes are more standard string delimiters in SQL syntax.

You can combine a variable with a string with string concatenation:

$string = "blah blah (" . $term . ") yadda yadda";

This uses the string concatenation operator . to combine three strings. The first and third string use double-quotes. The middle one is your variable. But this solution doesn't give you what you want because the resulting string looks like:

blah blah (term) yadda yadda

When what you presumably wanted was:

blah blah ('term') yadda yadda

So you have to put the single quotes inside anyway, so they end up in the result:

$string = "blah blah ('" . $term . "') yadda yadda";

Finally, the string concatenation is not necessary in any modern version of PHP. I always find it puzzling why people continue to use this method, probably because it's in some old books on PHP. You can now do this more simply:

$string = "blah blah ('$term') yadda yadda";

If your variable is something more complex like an array element or something, you might have to enclose it like this:

$string = "blah blah ('{$term[0]}') yadda yadda";

All of the above just addresses the issue of combining PHP variables with PHP strings. You should understand how to do that.

But you should also understand that for SQL queries as strings, it's better to use query parameters. Parameters makes the whole issue of combining variables with queries much simpler. You don't have to worry about quotes or escaping.

$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( ? IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");

Replace your variable with a placeholder ?. Don't put quotes around the placeholder inside your SQL string.

Then pass your variable to the prepared query separately, when you execute:

$results->execute([$term]);

That's PDO usage. I can't tell if you're using PDO or Mysqli from your code. Mysqli is a little bit different:

$results->bind_param('s', $term);
$results->execute();

Generally true - but in the case of an SQL query it would be better *not* to do any of that and use bound parameters instead. — CD001, Oct 12 '17 at 15:41

Major Productions · Answer 3 · 2017-10-12T16:02:13.353

1

PHP has two forms of strings. One where variables are interpolated (double quotes), one where they are not (single quotes). Depending on what you feel like using, you either need to:

Escape the double quotes you want to use (note the slashes before the quotes):

"SELECT * FROM ... \"$term\" ... "

Use single string for your string, and then concatenate the variable (note how the string is ended, the variable concatenated, and another string (the end of your query) is concatenated to that):

'SELECT * FROM ... "' . $term . '" ... "

In any event, this is something that would be solved by reading the manual.

EDIT: as others have said, your code is at serious risk for SQL injection. NEVER just take a value and stuff it into a database query, especially if that value comes from a form field or other external source. You need to parameterize your query in order to ensure that your query cannot be used as a means for SQL injection.

You should also check for the validity of your value(s) before using them. Are they empty? Do they contain the kind of data you're expecting?

edited Oct 12 '17 at 16:02

answered Oct 12 '17 at 15:38

Major Productions

5,914
13
70
149

**or** _properly_ use the prepare function that is already in the OP's code and parameterize the variables? (Or at least warn that the query is possibly vulnerable to SQL injection) – GrumpyCrouton Oct 12 '17 at 15:39
There's that, too, but given he doesn't know about how PHP handles strings, I figure that starting with the basics is the way to go. Seems like "throw crap at the wall by half-heartedly following tutorials" kind of code. Actually reading the manual (which includes parameterizing the variables) is a necessary first step for them, IMO – Major Productions Oct 12 '17 at 15:41
2

I get what you're saying @MajorProductionsLLC, but we are trying to turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). Even if it is just a warning about what they should learn to do in production code. – Jay Blanchard Oct 12 '17 at 15:53
Added some more re: SQL injection and data validation, with a link to the manual showing how it (MySQLi prepared statements) is done – Major Productions Oct 12 '17 at 16:04

score 1 · Answer 4 · answered Oct 12 '17 at 16:03

1

Your code is at risk of SQL Injection. You need to use Prepared Statement as below.

If you are using PDO:

$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( :term IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");
$stmt->bindParam(':term', $term);

If you are using MySQLi:

$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( ? IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");
$stmt->bind_param('s', $term);

This uses Parameter binding to try to prevent SQL Injections for more info check Prepared Statements

How can I prevent SQL injection in PHP?

answered Oct 12 '17 at 16:03

mega6382

9,211
17
48
69

1

I'm curious - why bother with bindParam() with PDO? Why not just use `execute([$term])`? – Bill Karwin Oct 12 '17 at 16:05
1

@BillKarwin You are right, I just thought it would be more clear to OP like this. – mega6382 Oct 12 '17 at 16:10
1

@BillKarwin That's what I do with the [simple wrapper function that I made](http://paragoncds.com/grumpy/pdoquery/). I'm curious if you have any thoughts about my function :) – GrumpyCrouton Oct 12 '17 at 16:48
1

@GrumpyCrouton, good job! Great that you are using PDO. I would have made a class for it, but it's nice and easy the way it is. – Bill Karwin Oct 12 '17 at 17:00
@BillKarwin I'm not familiar yet with how classes work, otherwise I would have. I need to learn one of these days. – GrumpyCrouton Oct 12 '17 at 17:43
@BillKarwin Sorry for bothering you again. I wanted to attempt to turn this into a class like you had mentioned, and I started that [on my github](https://github.com/GrumpyCrouton/GrumpyPDO) - I wanted to see if there was anything you could see that I could improve as this is the first class I have ever written. I'm not sure if I did everything correctly. – GrumpyCrouton Oct 12 '17 at 20:20

score 0 · Answer 5 · answered Oct 12 '17 at 20:15

Two things. Added: $term = '+'.$term.'*'; Changed to: AGAINST(? IN BOOLEAN MODE) AND Changed to: execute([$term]); I do this for all my PDO queries, just not this one, so thanks for that.

Meanwhile, I was using htmlentities to clean the $_POST variables which ended up changing the double quote to the ampersand quot semi-colon version which I assume MySql does not recognize when it looks at the string. Instead I changed that to $term = strtolower(strip_tags($_POST['term']));

So, the two changes seemed to fix the issue and make the query more secure. Thanks for the help.

Giacomo M · Answer 6 · 2017-10-12T15:38:17.777

-2

You have to use the variable inside the string in a different way. Use dots to concatenate a string with a variable.
If you need to use doublequote in a doublequote string, just escape doublequote with backslash like this:

\"

Said that, your code becomes:

$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( \"" . $term . "\" IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");

edited Oct 12 '17 at 15:38

answered Oct 12 '17 at 15:32

Giacomo M

4,450
7
28
57

Do or do not, there is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Oct 12 '17 at 15:34
@JayBlanchard There is no reason for downvoting an answer if you write "try this". The answer was correct. – Giacomo M Oct 12 '17 at 15:44
I did not DV your answer, just made a suggestion to help you improve your answers. – Jay Blanchard Oct 12 '17 at 15:45
Ok, thanks for the suggestion. – Giacomo M Oct 12 '17 at 15:46
@GiacomoMasseroniChiaro Actually, downvoting means "not useful" - which, in the context of StackOverflow - answers without explanations are not generally very useful for people other than OP. OP is not the main focus, the community is. Therefore, a downvote is totally justified for any reason that a user may think an answer is not useful. – GrumpyCrouton Oct 12 '17 at 15:59
@GrumpyCrouton what is usefull is subjective, i don't want to polemize with you – Giacomo M Oct 12 '17 at 16:02
@GiacomoMasseroniChiaro I'm not sure which controversy there is. I agree with you that useful is subjective, and that was my actual point. – GrumpyCrouton Oct 12 '17 at 16:03
1

I did not downvote, but I find this solution to be the worst possible solution even though it works. It uses nonstandard double-quotes in the SQL string, which means they must be escaped. Then it breaks the string unnecessarily to concatenate the variable. This code is hard to understand, tedious to write, and ugly to look at. – Bill Karwin Oct 12 '17 at 16:21
You have to escape string anyway if you dont use PDO with params. I just answered the question about not getting PHP syntax error. About the hard code, again is subjective. For you is hard to understand. – Giacomo M Oct 12 '17 at 16:30

score -2 · Answer 7 · answered Oct 12 '17 at 15:32

The problem is that you have double quotes within a double quoted string.

Change the quotes around $term to single quotes.

$results = $dbh->prepare("
SELECT * FROM images 
WHERE 
MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( '$term' IN BOOLEAN MODE)
ORDER BY copyright, images.imgName, images.sortOrder ASC");

score -2 · Answer 8 · answered Oct 12 '17 at 15:34

-2

Try this one may be it help

$results = $dbh->prepare("
SELECT * FROM images 

WHERE 

MATCH(imgTitle,imgDescr,copyright,keywords) 
AGAINST( '".$term."' IN BOOLEAN MODE)

ORDER BY copyright, images.imgName, images.sortOrder ASC");

answered Oct 12 '17 at 15:34

Deviser

83
1
11

Do or do not, there is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Oct 12 '17 at 15:35
Not to mention the obvious SQL Injection issue that was not even brought up in the answer at all. – GrumpyCrouton Oct 12 '17 at 15:35
ok then it will help for sure it help me too. – Deviser Oct 12 '17 at 15:36

php search mysql index by phrase

8 Answers8

Warning!