Search code examples
phpmysqlquotation-marks

mySQL problem when retrieving result with quotation marks

I'm having a problem with an SQL query on a php page.

I'm pretty new to php so stick with me.

I have successfully created a query to select everything, but I want to include a WHERE in the query. The trouble is that the result includes quotation marks because it is html code.

I'm sure this is simple but can anyone help... problem code below:

$result = mysql_query("SELECT * FROM myTable WHERE text = '<span class="myclass">Here is my text</span>'");
Solution

  • Short answer

    Escape the quotation marks:

    $result = mysql_query("SELECT * FROM myTable WHERE text = '<span class=\"myclass\">Here is my text</span>'");

    Correct answer

    You are almost certainly building this query dynamically (i.e., the string <span class=\"myclass\">Here is my text</span> is in a variable, let's call it $searchTerm).

    Do not, EVER, execute SQL queries with embedded variables without escaping the variable contents first. This will not only prevent such problems, but it will also prevent SQL injection attacks against your application.

    Assuming the $searchTerm variable as mentioned above, the correct way would be:

    $query = sprintf('SELECT * FROM myTable WHERE text = \'%s\'',
                 mysql_real_escape_string($searchTerm));
$result = mysql_query($query);

    Totally correct answer

    Use PHP's PDO extension, which provides easy access to this functionality (read up on variable binding) and is also 100% secure (which mysql_real_escape_string is not -- but the topic is way too advanced to discuss here).