How to disable % from outputting everything

Question

Hey I have an search field where I am searching something from my database, now I saw the problem after testing that if I put "%" in the search field it will output everything that I have ready for searching. Is there a way to disable this?

<h3>Search Share Details</h3>
<p>You may search either by company name or issue date</p>

<form name = "search" method = "get">
<input type = "text" name = "share" size = "40" maxlength="50">
<input type = "submit" value = "Search">
</form>

Getting contents connecting to DB, fetching results and printing

function get_contents() {

   if(isset($_GET['share']))
    {
        $conn = db_connect();
        $shares = get_shareSearch($conn);
        db_disconnect($conn);
        $contents = array('shares' => $shares);
        return $contents;
    }
    else
    {
        $conn = db_connect();
        $shares = get_share($conn);
        db_disconnect($conn);
        $contents = array('shares' => $shares);
        return $contents;
    }
}


function print_contents($contents) 
{

    if(count($contents['shares']) == 0)
    {
        echo "<script type = 'text/javascript'>alert('Sorry but share is not found! Q_Q');</script>";

    }
    else
    {
    ?>   
        <table>
            <tr>
                <th>Company Name</th>
                <th>Rate</th>
                <th>Issue Date</th>

            </tr>
    <?php
    foreach ($contents['shares'] as $share) 
    {
        print "<tr>";
        $identifier = urlencode($share['SHAREID']);
        print "<td><a href='share-details.php?id={$identifier}'>{$share['COMPANY']}</a></td>";
        print "<td>{$share['RATE']}</td>";

        $issue_date = $share['ISSUE_DATE'];
        $issue_date = $issue_date === NULL ? "&lt; not available &gt;" : $issue_date;
        print "<td>{$issue_date}</td>";
        print "</tr>";
    }
    ?>
        </table>
    <?php
    }
}
//require("shares.php");
require("search.php");
?>

Query itself

function get_shareSearch($conn) { 
$id = "";
if(isset($_GET['share'])){$id = $_GET['share'];}
$statement = db_create_statement($conn, "SELECT DISTINCT * FROM shares  WHERE(company LIKE '{$id}' OR issue_date LIKE '{$id}')");
$resultset = db_fetch_resultset($statement); 
return $resultset;

}

If I entered `%; DROP TABLE shares` in the search box, chances are I'd delete the entire table ? — adeneo, Apr 25 '15 at 16:31
possible duplicate of [Escaping MySQL wild cards](http://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards) — Huey, Apr 25 '15 at 16:32
What driver are you using? Use prepared statements if available, if not consider switching to a driver that can use prepared statements. — chris85, Apr 25 '15 at 16:37

Huey · Accepted Answer · 2015-04-25T16:47:22.487

1

Escape it

This refers to putting a character in front of it to denote it's meant to be taken literally:

Original Statement

SELECT * FROM ikeaTable WHERE chair LIKE '5% off';

Escaped Version

SELECT * FROM ikeaTable WHERE chair LIKE '5\% off' ESCAPE '\';

YOURS

SELECT DISTINCT * FROM shares WHERE(company LIKE '\%{$id}' OR issue_date LIKE '\%{$id}') ESCAPE '\'

edited Apr 25 '15 at 16:47

answered Apr 25 '15 at 16:31

Huey

5,110
6
32
44

so basically my query would be like `"SELECT DISTINCT * FROM shares WHERE(company LIKE '\%{$id}' OR issue_date LIKE '\%{$id}') ESCAPE '\'" ?` – Nico12345 Apr 25 '15 at 16:45

score 0 · Answer 2 · answered Apr 25 '15 at 16:34

0

I don't know which Database library you are using, but you certainly need to escape the parameters that you include into the query. If not escaped, MySQL will understand % as a special character that basically means 'match anything'.

I would suggest you read the database library documentation (or the code) to see how to include query parameters into your statement or how to escape them directly.

answered Apr 25 '15 at 16:34

maalls

749
8
20

I am using My Sql so basically my query would be like `"SELECT DISTINCT * FROM shares WHERE(company LIKE '\%{$id}' OR issue_date LIKE '\%{$id}') ESCAPE '\'" ` ? – Nico12345 Apr 25 '15 at 16:45
Maybe try to change the query to "SELECT DISTINCT * FROM shares WHERE company LIKE :share OR issue_date LIKE :share " and add after in the code db_bind($statement, ':share', $id); – maalls Apr 25 '15 at 16:52

How to disable % from outputting everything

2 Answers2