Mysql condition becoming string after adding variable-CodePudding

Mysql condition becoming string after adding $city OR $findname variable As I ad ("") '".$city."' rest of part becoming invalid

$findname = $_POST['findname'];
                                    
$city = $_POST['city'];
                                            
$show_details = $wpdb->get_results("SELECT  * from directory WHERE city LIKE '".$city."' OR findname LIKE '".$findname."'");
    
foreach ($show_details as $k => $v) {
   
    echo $v->name . "<br />";
}

CodePudding user response：

Use like this

$show_details = $wpdb->get_results("SELECT * FROM directory WHERE city LIKE '",$city,"' OR findname LIKE '",$findname,"'");

CodePudding user response：

First of all, you can test whether your assumptions are correct, via:

$show_details = $wpdb->get_results("SELECT  * from directory WHERE city LIKE 'Chicago' OR findname LIKE 'John Doe'");

This should be syntactically correct. So, since your query fails, it is highly probable that you have an issue with the value itself, like the Chicago you have might be enclosed by single quotes, like 'Chicago'. That would result in something of the like of

$show_details = $wpdb->get_results("SELECT  * from directory WHERE city LIKE ''Chicago'' OR findname LIKE 'John Doe'");

which is NOT syntactically correct. Seeing this it become clear that single quotes need to properly handled, that is, your program needs to reliably differentiate the single quotes you use to encapsulate your textual information from the single quotes that can be part of the data.

In our case, single-quote-issues are manifesting themselves in some fixable bug. But what if a malicious programmer would find out that your code does not properly differentiate the single quotes you wrap around textual data from single quotes that are part of the data? They could intentionally add single-quotes to the search terms or other data to end your search command and they would also add a new command of malicious code. For example, your data could be Chicago'; delete from directory;. That appended as the value of $city will result in

$show_details = $wpdb->get_results("SELECT  * from directory WHERE city LIKE '`Chicago'; delete from directory`;' OR findname LIKE 'John Doe'");

It would then run some search command, remove directories from your database and then run some invalid command that the hacker would not care about. This is a serious danger called SQL Injection.

You can add PDO to your code, so your query would look like

"SELECT * FROM directory WHERE city LIKE ? OR findname LIKE ?"

and PDO would handle the quote escaping for you. Since you almost certainly have quotes wrapped around your Chicago search term, your query will likely not find the records you want, but it will be syntactically correct. In order to fix the other issue that you have, you will need to make sure that whatever initializes the value of city will not artificially wrap quotes around it.