prototype ajax not properly executing query

Question

So I decided to start using prototype and here's my first question. I'm trying to send out an ajax request to a php page which updates s single record. When I do this by hand (ie: typing the address + parameters it works fine but when I use this code from javascript:

var pars = 'trackname=' + track + '&tracktime=' + time;

new Ajax.Request('php/setSongTime.php', {
method: 'get',
parameters: pars,
onSuccess: function(transport){
  var response = transport.responseText || "no response text";
  alert("Success! \n\n" + response);
  },
onFailure: function(){ alert('Something went wrong...') }

The onSuccess fires and displays the correct information from php, but the update is not made. What the php returns is the UPDATE string, so I'm checking the parameters and they look fine. Does anyone see a problem? Thanks...

Total javascript:

/*This file handles all the user-based computations*/

//variable declarations to be used throughout the session
var untimedSongArray = [];

function beginProcess(){

new Ajax.Request('php/getUntimed.php', {
method: 'get',
onSuccess: function(transport){
  var response = transport.responseText || "no response text";
  untimedSongArray = response.split("+");  
  alert(response);
  getFlashMovie("trackTimer").timeThisTrack(untimedSongArray[0]); 
  //alert("Success! \n\n" + response);
  //var html = response;
  },
onFailure: function(){ alert('Something went wrong...') }

});
}

function getFlashMovie(movieName) {
  var isIE = navigator.appName.indexOf("Microsoft") != -1;
  return (isIE) ? window[movieName] : document[movieName];  }

function setSongTime(track, time){
  alert("track " + track + " has a time of " + time);
  //$.get("php/setSongTime.php", { trackname: track, tracktime: time } );
  var pars = 'trackname=' + track + '&tracktime=' + time;

  new Ajax.Request('php/setSongTime.php', {
  method: 'get',
  parameters: pars,
  onSuccess: function(transport){
    var response = transport.responseText || "no response text";
    alert("Success! \n\n" + response);
    },
  onFailure: function(){ alert('Something went wrong...') }
  });
}

Total php code:

<?php

//turn on error reporting
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);
//header('Content-Type: text/xml');

/////////////Main script
//pull variables
//need to do some error checking here
$trackname = ($_GET['trackname']);
$tracktime = ($_GET['tracktime']);

//remove leading track information
$trackname = str_replace('../music_directory/moe/moe2009-07-18/', '', $trackname);
$trackname = str_replace('.mp3', '', $trackname);
//echo $trackname;

//connect with database
$con = mysql_connect("localhost","root","");
if(!$con){
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("musicneverstopped", $con);
//end connecting to database

//////////////////////////////////////////

//update given song time
$sql = "UPDATE songs SET length = ".$tracktime." WHERE unique_song_id = ".$trackname;
echo $sql;
mysql_query("UPDATE songs SET length = '$tracktime' WHERE unique_song_id = '$trackname'");

//error check
//if(!$attempt){
//die(mysql_error());
//}

//////////////////////////////////////////

//close database connection
mysql_close($con);//close mysql connection


?>

Anyone see any failing errors?

Answer 1

Try echoing the exact same SQL you actually run in mysql_query (store it in $sql then pass that into the query, instead of writing out the query twice).

Then try running the query that gets echoed out in the response directly in the mysql command line on your server and see what happens.

---

Also, just to echo Max on the importance of escaping your SQL queries, I would add to the input sanitisation that you should use bind variables in your query, rather than just concatenating your user input with the rest of the SQL.

Something like this would ensure your variables are suitably escaped to avoid an SQL injection attack.

$sql = "UPDATE songs SET length = '%s' WHERE unique_song_id = '%s'";
$query = sprintf(
    $sql,
    mysql_real_escape_string($tracktime),
    mysql_real_escape_string($trackname)
);
mysql_query($query);

Answer 2

Found it! Somehow I was getting an extra space before the finalized $trackname. ltrim fixed it right up. Thanks to everyone and thanks to those that mentioned security features. I'll definitely implement those. Dan