CODEWITHSUNDEEP
phpmysql
jinzoo
jinzoo

Reputation: 133

php adds slashes when concatenating strign to generate mysql query

Why is this:

$query = "SELECT Used, Date FROM table WHERE Name = '".$name."'";

Producing this:

'SELECT Used, Data FROM table WHERE Name =\'name_value\''

I checked php.ini and:

magic_qoutes_gpc = off
magic_quotes_sybase = off
magic_quotes_runtime = off

Edit:

The variable $name comes from select (drop-down menu) element through a jquery post:

$.ajax({
    type: "POST",
    url: "chart_handler.php",
    data: {'volumeName': $('select[name="Volumes"] option:selected').val()},        
    success: function (results){
        //console.log(results);
        alert(results);
        plotVolumeChart(results);
        }
});

And in chart_handler.php I have:

if (isset($_POST['volumeName'])){

    $name = $_POST["volumeName"];

    // The usual mysql connection stuff, then:

    $query = "SELECT `Used`, `Date` FROM `volumes2` WHERE `Name` = '".$name. "'";

for debugging and where I see the slashed being added:

    file_put_contents( 'output_debug_sql' . time() . '.log', var_export( $query, true));

Upvotes: 1

Views: 304

Answers (2)

aelbuni
aelbuni

Reputation: 311

I tried reproducing your issue, and it seems that var_export is generating the backslash, because it simply wraps that output string with single quotation as follows:

var_export( $query);

Output:

'SELECT `Used`, `Date` FROM `volumes2` WHERE `Name` = \'test\''

But if you echo the variable instead:

$query = "SELECT `Used`, `Date` FROM `volumes2` WHERE `Name` = '".$name. "'";

echo $query;

This would be the output:

SELECT `Used`, `Date` FROM `volumes2` WHERE `Name` = 'test'

Which is exactly as expected. Another point that I noticed in your code is that you do not need to add the escape quotes (`), so the following query should do the job:

SELECT Used, Date FROM volumes2 WHERE Name = 'test';

I suggest to double check your query in either phpmyadmin or MySql Workbench before you use it in your code, just to make sure that nothing is missing.

I really don't see any problem with the jQuery part, and as a proof of concept implementation, the following snippet (test.php) should not generate any back-slashes when it executes the AJAX request and print the result out:

<html>
   <head>
      <script src="https://code.jquery.com/jquery-2.1.4.min.js"></script>
   </head>
   <body>

<?php 

if (isset($_POST['volumeName'])){
    $name = $_POST["volumeName"];
    $query = "SELECT `Used`, `Date` FROM `volumes2` WHERE `Name` = '".$name. "'";
    echo $name."<br>";
    die();
}

?>


<select name="Volumes">
  <option value="volvo">Volvo</option>
  <option value="saab">Saab</option>
  <option value="opel">Opel</option>
  <option value="audi">Audi</option>
</select>

<div id="result">
</div>

<a href="javascript:submit_onclick()">Submit</a>

<script>

function submit_onclick()
{
$.ajax({
    type: "POST",
    url: "index.php",
    data: {'volumeName': $('select[name="Volumes"] option:selected').val()},        
    success: function (results){
        //console.log(results);
        $("#result").html(results);
        }
});
}

</script>

</body>
</html>

So try to:

  1. Remove the escape quotes (```) and see if your problem is fixed.

  2. Wrap your query with double quotation (") instead of single quotation ('), and this way var_export will not have to spit out the backslash. Do it as follows:

    $query = 'SELECT Used, Date FROM volumes2 WHERE Name = "'.$name. '"';

Best of luck

Upvotes: 1

teravice
teravice

Reputation: 33

I know you have magic_quotes_gpc=off but try using mysql_real_escape_string($variable) instead of just $variable. If it works then something else is going on.

Upvotes: 0

Related Questions