[SOLVED] - Updating SESSIONS in db with SQL in $data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

[SOLVED] - Updating SESSIONS in db with SQL in $data

Post by anjanesh »

Having some trouble writing a $sql statement into session table.
No errors,notices or warnings from PHP side and no errors from MySQL side.

Code: Select all

function session_write($id,$data)
 { 	
 	if (substr(trim($data),0,6)=="SELECT")
 	 $datum=addslashes(trim($data));
 	else
 	 $datum=$data;
 	//echo $datum;
 	if (mysql_num_rows(mysql_query("SELECT * FROM Sessions WHERE id='".$id."'"))==0)
 	 $SessRes=mysql_query("INSERT INTO Sessions VALUES('".$id."',NOW(),'".$_POST['User']."','".$datum."')");
 	else
  	 $SessRes=mysql_query("UPDATE Sessions SET data='".$datum."',`time`=`time` WHERE id='".$id."'");
 	if (!$SessRes) die (mysql_error().'<BR><BR>'.$datum);
 	return true;
 }
When called for writing sql statement into table,
In the first if-else it enters the first block as it should and $datum contains the add-slashed version of $data which is ok. In the second if-else, it enters the second block which is correct. The problem is $datum NEVER enters the `data` field. If I replace SET data='".$datum."',`time`=`time` with SET data='dummy',`time`=`time`, data gets the value dummy. So no problem with the sql statement.
But when a sql statement is to be written into a table...nothing happens - no error, warning - nothing. If I drop `time`=`time` even time gets updated to NOW() which it should since time is of type timestamp.
Any ideas ?
Thanks
Last edited by anjanesh on Fri Sep 03, 2004 7:08 am, edited 2 times in total.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

The session handler is run after the output stream is closed. You'll need to save any debug info to a file because you won't even get an error message.

$data is serialized $_SESSION array and needs to be addslashed()'ed.

Here's my session write function maybe it will give you some ideas:

Code: Select all

function mysql_session_write( $session_id, $data ){

    global $_CFG;

    $data = addslashes( $data );
    

    $result = @mysql_query( "UPDATE "
        . $_CFG['db_name'] . $_CFG['db_prefix'] . "sessions "
        . "SET "
        . ( $_CFG['current_session_id'] != $session_id ? "id='$session_id', " : "" )
        . "last_touched='" . time() . "', "
        . "data='$data'"
        . ( isset($_SESSION['session_lifetime']) === true
            ? ", lifetime='" . $_SESSION['session_lifetime'] . "' "
            : "" )
        . "WHERE id='".$_CFG['current_session_id']."'" );
        
    if( $result === false ){

        return false;

    }

    if( mysql_affected_rows() ){

        $_CFG['current_session_id'] = session_id();
        return true;

    }

    $result = @mysql_query( "INSERT INTO "
        . $_CFG['db_name'] . $_CFG['db_prefix'] . "sessions "
        . "("
        . "id, "
        . "last_touched, "
        . "data"
        . ( isset($_SESSION['session_lifetime']) ? ", lifetime" : "" )
        . ") VALUES ("
        . "'$session_id', "
        . "'" . time() . "', "
        . "'$data'"
        . ( isset($_SESSION['session_lifetime']) === true
            ? ", '" . addslashes($_SESSION['session_lifetime']) . "'"
            : "" )
        . ")" );
        
    if( $result === false ){

        return  false;

    } else {

        return true;

    }
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

In PHP4 at least, the session write handler is called at the end of the script. It is not called after the stream is closed. You can have debug output in the function.

$data is serialized. However, it doesn't require addslashes if you convert all occurances of your sql string, in your case single quotes, into two consecutive single quotes. In short:

Code: Select all

$sql = "update table_name set foo = '" . str_replace('''','''''',$string) . "' where bar = 2";
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

PHP Manual wrote:Note: The "write" handler is not executed until after the output stream is closed. Thus, output from debugging statements in the "write" handler will never be seen in the browser. If debugging output is necessary, it is suggested that the debug output be written to a file instead.
Is there a significant improvement using str_replace over addslashes?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it may say that in the docs.. but I can output text just fine and have done it on many servers... :)
Is there a significant improvement using str_replace over addslashes?
overly, there's probably little difference.. str_replacing just the 1 character may be slightly faster, but that's roughly it.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

Hmm...my sessions seem to work as advertised which makes debugging the handler no fun.

Feyd do use session_write_close()? Been wondering if that would close the session immediately or as normal?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I use session_set_save_handler.. and let it do it's thing :)
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I tried both (separately) :
$datum=addslashes(trim($data));
$datum=str_replace('''','''''',$data);

Code: Select all

$SessRes=mysql_query("UPDATE Sessions SET data='".$datum."',`time`=`time` WHERE id='".$id."'");
write nothing to $data

Code: Select all

$SessRes=mysql_query("UPDATE Sessions SET data='test".$datum."',`time`=`time` WHERE id='".$id."'");
writes test to data
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

... and you're sure $datum had something in it?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Yes. I checked that already.
On echoing $sql in the write function : (Im showing the first and last bit of the $sql)

Code: Select all

UPDATE Sessions SET data=' SELECT HouseMain.HouseID FROM House_Main HouseMain,Agents_House AgentHouse,Agent Agent WHERE HouseMain.Enabled=''y'' AND ....... HouseMain.StateID=''13'' ',`time`=`time` WHERE id='6b083a3324b02a08ac2a85bbb9893290'
By the way I tried base64_encode($data) instead just to avoid the addslashes and other string related stuff : (first and last bit)

Code: Select all

UPDATE Sessions SET data='DQog................nICA=',`time`=`time` WHERE id='6b083a3324b02a08ac2a85bbb9893290'
still nothing gets written. Its not the string part I think.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why isn't that serialized data? how are you calling this function? is it actually through the session system, or directly?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Im calling this in the search result page like this (tried all 3) :

Code: Select all

if (isset($PHPSESSID)) session_write($PHPSESSID,$sql);
if (isset($PHPSESSID)) session_write($PHPSESSID,addslashes($sql)); // I Removed addslashes from session_write function
if (isset($PHPSESSID)) session_write($PHPSESSID,base64_encode($sql));
Neither does this work : if (isset($PHPSESSID)) session_write($PHPSESSID,"test");
So it cant be a string issue.
And whats this 'serialized data' ?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Forget about the function session_write. I tried doing this in the place where Im calling session_write($PHPSESSID,addslashes(addslashes($sql)))

Code: Select all

$sql1="UPDATE Sessions SET data='".addslashes($sql)."' WHERE id='".$PHPSESSID."'";
$sql2="INSERT INTO Sessions VALUES ('0',NOW(),'test','".addslashes($sql)."')";
$sql2 works. $sql1 doesnt !
I checked the PHPSESSID with id in db - same
Even tried :

Code: Select all

$sql1="UPDATE `Sessions` SET data='".addslashes($sql)."'";
Only one entry is there is the table and still no luck.
Entered $sql1 in phpMyAdmin and it got updated !
Simplest form in the script : mysql_query("UPDATE Sessions SET data='a'"); des not work
This is terribly confusing
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

After some debugging, I finally found out what was going on.
session_write() works fine inserting the data etc.
I did a echo session_read() and it showed up. But it still doesnt show up in the db. For some reason session_write() is executed AGAIN just before the script ends or before die() etc ! This is replacing it with empty $data.

session_write() gets executed even if I dont call it at the end of the script with data="". Thus in my script it got executed TWICE - 1st my data and at the end automatically.
Anyone knows why this is happening ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

session_write is ALWAYS called at the end of the page when you have it set in the session handlers.. you aren't supposed to call it directly, ever. Only store data into the $_SESSION superglobal.
Post Reply