Apostrophes Are Breaking My MySQL Query in PHP

apostrophes are breaking my mysql query in PHP

You have to $name = mysql_real_escape_string($name); before that line.

You might also want to read up on SQL Injections, since your inputs are clearly unsanitized.

A very odd Apostrophes MySQL PHP error

I would seriously consider fixing the data in your database.

Now, having said that, MySQL recognizes both \' and '' as an escaped apostrophe. That is, unless the server mode is set to use strict SQL, in which case only '' is recognized.

The data is most certainly getting double-escaped on entry, most likely once when the user enters it (by magic_quotes_gpc, which uses addslashes) and again by mysql_real_escape_string when you call it.

This would turn Assassin's Creed into Assassin\\\'s Creed, which would ultimately get stored as Assassin\'s Creed.

I highly recommend disabling magic_quotes_gpc if you can, as it causes more problems than it fixes.

Apostrophe does not work in mysql/PHP

All you need to escape ' like below

$_REQUEST['text'] = mysql_real_escape_string($_REQUEST['text']);

How to escape apostrophe in php variable for select query

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);

---

• Consider using parametrized queries such as mysqli with prepared statements, or PDO with prepared statements instead.

---

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.

How to deal with an apostrophe while writing into a MySQL database

The process of encoding data which contains characters MySQL might interpret is called "escaping". You must escape your strings with mysql_real_escape_string, which is a PHP function, not a MySQL function, meaning you have to run it in PHP before you pass your query to the database. You must escape any data that comes into your program from an external source. Any data that isn't escaped is a potential SQL injection.

You have to escape your data before you build your query. Also, you can build your query programmatically using PHP's looping constructs and range:

// Build tag fields
$tags = 'tag' . implode(', tag', range(1,30));

// Escape each value in the uniqkey array
$values = array_map('mysql_real_escape_string', $uniqkey);

// Implode values with quotes and commas
$values = "'" . implode("', '", $values) . "'";

$query = "INSERT INTO alltags (id, $tags) VALUES ('', $values)";

mysql_query($query) or die(mysql_error());

apostrophe error

You are seeing error because an apostrophe has special meaning in Mysql. As Ian said you have to use mysql_real_escape_string on $strquery

Not using escaping will lead to serious SQL injection security issues. http://www.unixwiz.net/techtips/sql-injection.html

function ExecuteQuery( $strquery ) {
  $rs = mysql_query(mysql_real_escape_string($strquery)) or 
        die('<br><br>An error occured upon executing query.<br>Please notify the web developer about this error.<br><br>NOTE: '.mysql_error());
  return $rs;
}

SQL query breaks due to apostrophe

This is a classic SQL injection flaw. If the value of $value is controlled by the user, they can basically do anything right now. The quote in the scraped string breaks out of the quoted environment within the SQL string, and thus allows for arbitrary SQL commands to be executed.

The short answer is, use mysql_real_escape_string.

Of course, there's the obligatory "mysql_* is deprecated, use mysqli or PDO" part of this too.

MySQL error when inserting data containing apostrophes (single quotes)?

Escape the quote with a backslash. Like 'Kellogg\'s'.

---

Here is your function, using mysql_real_escape_string:

function insert($database, $table, $data_array) { 
    // Connect to MySQL server and select database 
    $mysql_connect = connect_to_database(); 
    mysql_select_db ($database, $mysql_connect); 

    // Create column and data values for SQL command 
    foreach ($data_array as $key => $value) { 
        $tmp_col[] = $key; 
        $tmp_dat[] = "'".mysql_real_escape_string($value)."'"; // <-- escape against SQL injections
    } 
    $columns = join(',', $tmp_col); 
    $data = join(',', $tmp_dat);

    // Create and execute SQL command 
    $sql = 'INSERT INTO '.$table.'('.$columns.')VALUES('. $data.')'; 
    $result = mysql_query($sql, $mysql_connect); 

    // Report SQL error, if one occured, otherwise return result 
    if(!$result) { 
        echo 'MySQL Update Error: '.mysql_error($mysql_connect); 
        $result = ''; 
    } else { 
        return $result; 
    } 
}

Escaping single quote in PHP when inserting into MySQL

You should be escaping each of these strings (in both snippets) with mysql_real_escape_string().

http://us3.php.net/mysql-real-escape-string

The reason your two queries are behaving differently is likely because you have magic_quotes_gpc turned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., "O'Brien" -> "O\'Brien").

Once you store the data, and subsequently retrieve it again, the string you get back from the database will not be automatically escaped for you. You'll get back "O'Brien". So, you will need to pass it through mysql_real_escape_string().

---

Related Topics