CODEWITHSUNDEEP
phpmysqlstringmysqliescaping
Lonergan6275
Lonergan6275

Reputation: 2038

How to escape apostrophe in php variable for select query

I have tried escaping the query string and the $variable containing the apostrophe using the mysqli_real_escape_string the variable value is coming form the database. I am getting the following error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'Shamrock Rovers%\' AND away_team like \'St Patrick's Athletic%\'' at line 1

The apostrophe is not getting escaped by the quotes around the comparison values is.

Here is the query as it appears in the PHP file:

    $homeTeam = filter_input(INPUT_GET, 'homeTeam', FILTER_SANITIZE_STRING);
    $homePlayers = "select * from players where team_name like $homeTeam";
    $homePlayers = mysqli_real_escape_string($dbc, $homePlayers);
    $homePlayersResult = mysqli_query($dbc, $homePlayers);

And echoed out to the browser:

    select * from players where team_name like Shamrock Rovers

I have tried it a number of different ways with no variation in results i feel i am overlooking something simple. Thanks in advance.

Edit 1

Updated Code

$homeTeam = filter_input(INPUT_GET, 'homeTeam', FILTER_SANITIZE_STRING);          
$homeTeam = mysqli_real_escape_string($dbc, $homeTeam);
echo "<br>".$homeTeam."<br>";
$homePlayers = "select * from players where team_name like '$homeTeam%'";

$homePlayersResult = mysqli_query($dbc, $homePlayers);

This Script recieves 3 parameters from a processing script

header("location: ../scorer.php?gameWeek=$gameWeek&homeTeam=$homeTeam&awayTeam=$awayTeam");

outputs select * from players where team_name like 'St Patrick's Athletic%'

Edit 2

after entering the query in to mysql command window nothing happens when i submit the query once but when i enter it again i get the following err. enter image description here

Upvotes: 1

Views: 3228

Answers (1)

Funk Forty Niner
Funk Forty Niner

Reputation: 74219

like $homeTeam";

you need to quote that variable.

like '$homeTeam'";

or 

like '$homeTeam%'";

since this is a string, as per your like Shamrock Rovers

However I don't know why you're using

$homePlayers = mysqli_real_escape_string($dbc, $homePlayers);
                                               ^^^^^^^^^^^^

while escaping your query: (?)

$homePlayers = "select * from players where team_name like $homeTeam";
^^^^^^^^^^^^

You probably meant to use:

$homePlayers = mysqli_real_escape_string($dbc, $homeTeam);

Edit: (test)

This is what I used to successfully query a test table of mine, being "users".

<?php

$DB_HOST = 'xxx';
$DB_USER = 'xxx';
$DB_PASS = 'xxx';
$DB_NAME = 'xxx';

$Link = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($Link->connect_errno > 0) {
  die('Connection failed [' . $Link->connect_error . ']');
}

$_GET['homeTeam'] = "St Patrick's Athletic";

$username = $_GET['homeTeam'];

$homeTeam = filter_input(INPUT_GET, 'homeTeam', FILTER_SANITIZE_STRING);
$homePlayers = mysqli_real_escape_string($Link, $homeTeam);
$homePlayers = "select * from users where username like '$homeTeam%'";
$homePlayersResult = mysqli_query($Link, $homePlayers);

echo "Names found like: " . $username;

echo "<br>";

 while($row = mysqli_fetch_array($homePlayersResult)){

echo $row['username'];

echo "<br>";

echo "<a href=\"{$row['my_row']}\">".$row['my_row']."</a>";

echo "<br>";

}
  • Plus, make sure that your column is indeed VARCHAR and its length long enough and that your input is a "text type".

Sidenote:

You don't need this which will break your query:

$homeTeam = filter_input(INPUT_GET, 'homeTeam', FILTER_SANITIZE_STRING);

since you're already using mysqli_real_escape_string() to sanitize your input.

Something that we've discussed during our chat which was resolved, yet I did already mention the above before chatting which was the solution after all.

Upvotes: 5

Related Questions