-1

I'm trying to get all values from table products, that contains for example shirt.
In this case $thin is getting values from searach box "stxt".
It means that if $thin = shirt, I want to get shirt, t-shirt etc.
Right now, only thing that I get is only shirt, despite if I will use "LIKE" or "=" as operator in $sql statement.

$thin = $_POST['stxt'];
$thing = strtoupper($thin);
$sql = "select * from products where upper(productName) LIKE '$thing'";
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
DaRt
  • 1
  • 1
  • 3
  • 1
    `LIKE '%' || '$thing' || '%'` – jarlh Mar 03 '22 at 14:33
  • 5
    Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should always use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! – RiggsFolly Mar 03 '22 at 14:33

3 Answers3

0

In your query, put in your LIKE % before and after your variable. Like this "%$variable%". If you want there to be just a number of n characters before or after your variable, put the _ symbol n times. And for security reasons, try to use prepared statements.

Link sql like: https://sql.sh/cours/where/like

griCode
  • 1
0

can you use a regexp operator ?

$sql = "select * from products where productName regexp $thing";

Simon
  • 1
  • 1
-1
$thin = $_POST['stxt'];
$thing = strtoupper($thin);
$sql = "select * from products where upper(productName) LIKE '%$thing%'";

MSSQL needs % wildcards, other SQL dialects may differ. Alternatively you could do a REPLACE(productName,'$thing','') and compare the length of that to the original length. Either way it is going to be a full table scan unless you have full text indexes set up.

Aaron Reese
  • 544
  • 6
  • 18
  • You should not give an answer that includes a clear security risk. – KIKO Software Mar 03 '22 at 14:46
  • the risk has already been pointed out be a previous commenter, but yes it is not a good idea, however because this is using a wildcard search you can't parameterise the query so it would have to be a stored procedure which is probably beyond the skills of the OP if they are asking this question. – Aaron Reese Mar 03 '22 at 14:50
  • But [you can parameterise a wildcard search](https://stackoverflow.com/questions/16255657/pdo-prepared-statements-with-wildcards). Just bind to `"%$thing%"`. I'm sorry about repeating things... – KIKO Software Mar 03 '22 at 14:51