Wildcard SQL search php

Question

 <form name="attdate" action="keyword.php" method="post">
      Enter Keyword :<input name = "key">   
      <button  class='btn btn-lg btn-danger' type ="submit"> Search Records</button>

What I am trying to do is query my varchar fields with a keyword (wildcard) search:

The fields in the rows that I want to query are 'hs' 'nv' 'vsa' .

My PHP looks like this :

<?php


include 'config.php';

$key = ($_POST['key']);



$key = mysqli_real_escape_string($con,$key);



$sql = "SELECT * FROM handover WHERE hs LIKE "%'.$key.'%"
OR WHERE nv LIKE "%'.$key.'%"
OR WHERE vsa LIKE "%'.$key.'%"";


$result = mysqli_query($con,$sql);

$count=mysqli_num_rows($result);

if($count==0 ){

echo "</br></br></br></br></br></br></br><h2>Handover Details</h2><p> No Matching   

results found</p>";
}
else{
while($row = mysqli_fetch_array($result)) {
echo '</br></br></br></br></br>';

I think it might be where I am putting the %. The column is not part of the index is this a problem?

thanks in advance

**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Nov 19 '14 at 20:34

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

Change your quote order, place single quotes at the beginning and end of your query, or vice-versa.

$sql = 'SELECT * FROM handover WHERE hs LIKE "%'.$key.'%"
OR WHERE nv LIKE "%'.$key.'%"
OR WHERE vsa LIKE "%'.$key.'%"';

You also need to parameterise your queries rather than injecting variables directly into the query string. This will help you Prevent SQL Injection!

UPDATE: Example of parameterised query:

$mysqli = new mysqli("host", "user", "password", "database");// connection
$key = $_POST['key'];
$query = $mysqli->prepare("SELECT * FROM handover WHERE hs LIKE ? OR WHERE nv LIKE ? OR WHERE vsa LIKE ?"); // note how simple in this case, no quoting problems
$stmt->bind_param("sss", $key, $key, $key); // binding a string which replaces ?, once for each ? (repeats in your case)
$stmt->execute();

edited May 23 '17 at 12:27

Community

1
1

answered Nov 19 '14 at 20:29

Jay Blanchard

34,243
16
77
119

Hello I know this is cheeky but could someone put an example of the above code as a parametized query. I have put code on here before and been told the same thing and then had a very good go at parametising queries but failed. It seems people tell you your code is dangerous but do not tell you how to make it safe. I am not a programmer (Im a wannabee programmer). It took me enough reading just to get these unsafe queries to work. I find the link (php) confusing!. If no code can someone tell me an easy to follow tutorial? – Fintan Creaven Nov 19 '14 at 21:03
Just a simple example? it does not have to be for my anwser I just need to see what a normal post looks like? – Fintan Creaven Nov 19 '14 at 21:48
I've added an example @FintanCreaven – Jay Blanchard Nov 19 '14 at 22:19
@ JAY thanks for this. It does make sense looking at your example. Do you output the results in the same way? $result = mysqli_query($con (instead of your $mysqli),$query); I added the $query and $con is that correct?!! $count=mysqli_num_rows($result); if($count==0 ){ etc. – Fintan Creaven Nov 19 '14 at 22:32
I would use [`mysqli_stmt_fetch()`](http://php.net/manual/en/mysqli-stmt.fetch.php). You could use `$result` and `$con` as you have done...I just find it easier the other way. – Jay Blanchard Nov 19 '14 at 22:35
Thanks for help Jay I feel bad keep sapping your knowledge. Its not working yet but my question has somewhat gone beyond the scope of the original question thanks for your help. – Fintan Creaven Nov 19 '14 at 22:54
No worries. Post new questions if you get stuck! – Jay Blanchard Nov 19 '14 at 22:55

Wildcard SQL search php

1 Answers1