dealing with 's in a mysql query

[map200uk]

hi,

 

im having issues with my script:

 

my project resolves around digital audio files, and cataloguing the data in a mysql dataase, however some files are causing problems

 

i.e

 

...::::: : : ----------------------------------

...::::: : : ID3v1 : TAG :::::...

...::::: : : Title : Goodnight Goodnight����������� :::::...

...::::: : : Artist : Maroon 5����������������������:::::...

...::::: : : Album : It Won't Be Soon Before Long�� :::::...

...::::: : : Year : 2007 :::::...

...::::: : : Comment : ���������������������������� :::::...

...::::: : : Track : 8 :::::...

...::::: : : Genre : Pop :::::...

 

is the metadata from a song (not sure why it gets ????) and well, this is to be put in a mysql database, bt the ' cause problems, and cause errors in queries

 

i tried addslashes-but this ended up adding 10000s of \'s to the value

 

i was wondering if anyone has a suggestion?

 

my only idea is urlencode, urlencode($ID3->album) where $ID3->album holds the value of the album

 

any suggestions/comments or advice on how to deal with this are greatly appreciated

 

thanks

[map200uk]

tried with urlencode, and its still not right:(

...::::: : : ----------------------------------

...::::: : : ID3v1 : TAG :::::...

...::::: : : Title : Do You Think I'm Sexy��������� :::::...

...::::: : : Artist : Paris Hilton������������������:::::...

...::::: : : Album : Paris������������������������� :::::...

...::::: : : Year : 2006 :::::...

...::::: : : Comment : ���������������������������� :::::...

...::::: : : Track : 11 :::::...

...::::: : : Genre : Pop :::::...

...::::: : : ----------------------------------

song has a size of 6664030)

mpfweeeeeeID3v1x Object ( [file_name] => [tag] => TAG [title] => Do+You+Think+I%27m+Sexy%00%00%00%00%00%00%00%00%00 [artist] => Paris+Hilton%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00 [album] => Paris%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00 [year] => 2006 [comm] => %00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00 [track] => 11 [genre] => Pop [temp] => � )

thats all today folksInvalid query: 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 'm Sexy.mp3'' at line 1

 

firstly obviously all the extra junk added, but secondly it doesnt cure the issue with ' s

 

thanks

 

[map200uk]

i thought if i put the value in "" it would work but no:(

[pocobueno1388]

Well, if you use addslashes it will add all those slashes, but then when you want to retrieve the value you just use stripslashes on it and it will remove all of them.

 

You could also use mysql_real_escape_string() which will escape special characters in a string for use in a SQL statement.

http://php.net/mysql_real_escape_string

 

 

[btherl]

Did you really use urlencode()?  The error message you show is an error message from a string that was not urlencoded.  Can you post your actual code?

[map200uk]

btherl, yea i did mate

 

the thing is when i use addslashes/urlencode im getting anither error

 

anyway:

 

<?php
include 'oggtest.php';	
function findMusic($dir) {
     $musicList = array();
     $dirHandle = opendir($dir);
     $store="/opt/lampp/htdocs/mp3/";
     while (($file = readdir($dirHandle)) !== FALSE) {

     echo "<br>\n$file";
     echo "<br>\n $dir";
          if ($file == "." || $file == "..") continue;
          if (is_dir($dir . "/" . $file)) 
         {
                echo "<br>full dir \n $dir \n $file ";

                if(is_link($dir) || (is_link($file)))
                {       echo "<br>symlink found , ignoring symlink!!!";
                        echo "<br>$dir./$file \n is a symlink";
                        echo "<br>Symlink found on \n $file in \n $dir";

         } else {
                  $musicList=array_merge($musicList, findMusic($dir . "/" . $file));

        }
             }

          $extension = end(explode(chr(46), $file));
         // print $extension;

      /* if(is_link($file))
       {
       echo "<br>file is a symlink";
       } else {
       */

if(is_link($dir."/".$file))
       {
       echo "<br>$dir./.$file is a symlink!";

      } else {
       if (strtolower($extension) == "mp3" || strtolower($extension)== "ogg")
     
        {

        if(is_link($store.$file))
        { echo "<br>Symlink for \n $dir.$file already exists<br>";
        } else {
        echo "<br>Creating symlink for \n $file in \n $store";


        symlink($dir."/".$file,$store.$file);
        $link=$store.$file;
echo "<br>link is equal to \n $link";
     
   //$musicList[] = $dir . "/" . $file;
        $musicList[]=$link;

echo "<br> dir equal to \n $dir , file is \n $file";
        echo "<br> Loation of file aaaaaaaaaaaaaa";
        echo "<br> Symlink for \n $file is \n $store.$file";
        echo "<br> Symlink created location = $store.$file";
        $linkuse=str_replace("/opt/lampp/htdocs/","/../../../",$link);
        echo "<br>linkuse is equal to \n $linkuse";
echo "<br>";
        echo "<br>";
        echo "<br> VALUE EEEEEEE \n";
        echo $linkuse;
        $musicPath[]=$linkuse;
        }
        } 

       
          
     }

}
     closedir($dirHandle);
     return $musicList;

}
   

echo "<br><b> Please type in the directory you wish to scan for audio!<br></b>";
echo "<br><b> Note, permissions need to allow user nobody to acces the dir, else you will get errors!";
echo "<form action=$_SERVER[php_SELF]?page=finddir&action=scan method=POST><input type=text name=dir>";
echo "<input type=submit name=submit value=submit></form>";

include 'ID3v1x.php';

function addSong($type)
{

global $db;
global $ID3;
global $test;
global $value;
global $path;
if($type=="mp3")
{
$size=filesize($value);
echo "<br>song has a size of $size)";
echo "<br>mpfweeeeee";
print_r($ID3);
$title=$ID3->title;
$artist=$ID3->artist;
$genre=$ID3->genre;
$year=$ID3->year;
$album=$ID3->album;
$comment=$ID3->comment;
$track=$ID3->track; 


}elseif($type="ogg")
{
$size=filesize($value);
echo "<br>ogg filesize \n $size";
print_r($test);
$title=$test['Title'];
$artist=$test[Artist];
$album=$test[Album];
$year=$test[Date];
$comment=$test[Comment];
$track=$test[Tracknumber];
$genre=$test[Genre];



echo "OGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG";
}




$db->query("SELECT * FROM  Song WHERE SongTitle='$title' AND Path='$value'");
                $db->numResults();
                echo "\n <br> Resource ID: $db->result";

                if($db->rows > 0) // if the rows for the query > 0 we have a match, so we insert a record into song table
                                                        // using the albumID found from the query
                {
                        echo "<br>Entry with the same title and file path already added to database!<BR>";

                } else {

                echo "checking to see if album added";
                $db->query("SELECT * FROM album WHERE AlbumName='$album'");
                $db->numResults();
                if($db->rows>0){
                echo "we have a match";
                echo "\n <br> Lets use the current ID and insert the record";
echo "<br> Album ID : --- $db->albumID \n && Resource ID : -- $db->rows";
                $db->query("INSERT INTO Song VALUES ('NULL', '$title', '$year', '$genre', '$value', '$db->albumID','NULL','$path','$comment','$track','$type','$size')");


                } else {
                $db->query("INSERT INTO album VALUES ('NULL','$album','$artist')");
                echo "<br> New file found \n $ID3->path";
                echo "<br>Looks like the album is not added<br>";
                printf("Last inserted record has id %d\n", mysql_insert_id());
                $last_id = mysql_insert_id(); // set the last insert id on mysql (last auto increment id) to $last_id

                echo "<br> New Album inserted...ID: $last_id";
        echo "<br> Now time to insert song entry...";
        $db->query("INSERT INTO Song VALUES ('NULL','$title','$year','$genre','$value','$last_id','NULL','$path','$comment','$track','$type','$size')");  // insert th$
                }
}
// insert data into song table if we found an ID, the tag data i.e. NULL for ID (auto inc), title, year, genre
// path and album id

//$db->getResults("MYSQL_BOTH");












}
if($_POST[dir]!="")
{


//print_r(findMusic("$_POST[dir]"));



//print_r($_POST);
$test=findMusic("$_POST[dir]");

//print_r($test);

$ID3= new ID3v1x();
include 'db.php';

$db = new db();
$link = $db->connect("localhost","monty","some_pass");
$i=0;
foreach ($test as $value) {
   echo "<br><br><b>MP3 found => \n $value</b>";
        $i=$i+1;
echo "<br> \n $i";
if(strstr($value,"ogg"))	
{
echo "OGG FOUND";
echo "<br>$value \n is ogg";
$test=oggdata($value);
echo "<br><br><br><br>";
$title=$test['Title'];
$artist=$test[Artist];
$album=$test[Album];
$year=$test[Date];
$comment=$test[Comment];
$track=$test[Tracknumber];
$genre=$test[Genre];

       		echo "...::::: : File : $value       :::::...<br>";
                echo "...::::: : : ----------------------------------<br>";
                echo "...::::: : : Header :    :::::...<br>";
                echo "...::::: : : Title : $title  :::::...<br>";
                echo "...::::: : : Artist : $artist:::::...<br>";
                echo "...::::: : : Album : $album  :::::...<br>";
                echo "...::::: : : Year : $year    :::::...<br>";
                echo "...::::: : : Comment :$comment :::::...<br>";
                echo "...::::: : : Track : $track  :::::...<br>";
                echo "...::::: : : Genre : $genre  :::::...<br>";
                echo "...::::: : : ----------------------------------";

addSong(ogg);
print_r($test);
}elseif(strstr($value,"mp3"))
{
echo "<br>mp3 yes yes yes u know it";
  if($ID3->readtag($value) == true)
  //{
                echo "...::::: : File : $mp3_file       :::::...<br>";
                echo "...::::: : : ----------------------------------<br>";
                echo "...::::: : : ID3v1 : $ID3->tag    :::::...<br>";
                echo "...::::: : : Title : $ID3->title  :::::...<br>";
                echo "...::::: : : Artist : $ID3->artist:::::...<br>";
                echo "...::::: : : Album : $ID3->album  :::::...<br>";
                echo "...::::: : : Year : $ID3->year    :::::...<br>";
                echo "...::::: : : Comment : $ID3->comm :::::...<br>";
                echo "...::::: : : Track : $ID3->track  :::::...<br>";
                echo "...::::: : : Genre : $ID3->genre  :::::...<br>";
                echo "...::::: : : ----------------------------------";

                $path=$value;
    //            echo "<br>path is \n $path";
              $path=str_replace("/opt/lampp/htdocs/","/../../../",$value);
	$ID3->album=addslashes($ID3->album);
	$ID3->title=addslashes($ID3->title);
	$ID3->artist=addslashes($ID3->artist);
	$ID3->comm=addslashes($ID3->comm);

	addSong("mp3");

// echo "<br> new path is \n $path";
               
}
}
}
?>

 

i was also thinking about mysql_real_escape_string

 

this sounds like the best bet?

 

map

[map200uk]

just changed it to use mysql_real_escape_string and i still get errors:(

 

anyone able to offer some advice or ideas

 

thanks