Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Backup or Export/Transfer Might have Corrupted

Author  Topic 

jdsmith8
Starting Member

11 Posts

Posted - 2013-07-31 : 00:52:09
Hi All,

I got stumped on something strange regarding a SQL database function being called from a PHP file.

I transferred a site from one hosting company to another and downloaded all the web site files and exported the SQL database
as usual. Then on the new hosting platform uploaded the site files and imported that same SQL database and then changed the SQL
connection string to the new database configuration and evrything on the site looked to work just fine.

I then had a few people tell me they could not submit the contact request form and thought, well I have done SQL database transfers before without issues after changing the connection string to the new database settings.

If everything else was EXACTLY the same code, file names, etc.. why would the contavt form not submit?

I checked and in the SQL DB there are two CAPTCHA tables named: captchas and captcha_temp

The table named ' captchas ' has 3 fields labeled capid, question and answer. The table named ' captcha_temp ' has 4 fields labeled ctid, capid,sessid and rndkey .

Of course I spent hours going through all the PHP files that call to the SQL DB and could not find a field for something called $ans_key and then in a string see a $cap_ans, but that is not a field in the table named ' captcha ' that it looks how it is written.

In the PHP code here you can see a hash key or something for $ans_key, but do not see a field in any table in the SQL DB labeled $ans_key.

<?php
include("access/include/functions.php");
include("access/include/session.php");
require_once("captcha/class.captcha.php");
// ===================================================PRocess Mailform
///////// Contact /////////-->
$sessid=session_id();
foreach($_POST as $key=>$value) {
$$key = $value;
//echo"--// $key = $value<br>";
}

// echo"--------------------------------------// Session";
/* foreach($_SESSION as $key=>$value) {
$$key = $value;
echo"$key = $value<br>";
}
*/

//$ans_key='52c6cdb12f358c0ad6c4be46e4e62dac';

// $sessid=$_SESSION['sessid'];
//lets validate then destroy the temp record in the captchas

if(!$capid)$capid=$cap_id;
$db=$database->connection;
$captcha = new Text_Captcha($db);

if (!$captcha->validate($cap_ans,$capid,$ans_key)){
$step2=false;
$errmsg=true;
}else{
$captcha->deleteTempKey($capid, $sessid);
}

if($step2){

// LET's LOG this INFO ==============================================================//
$admin_errmsg=array();
$contact="$title $firstname $lastname";
$_SESSION['contact']="$title $lastname";

and so on...


Then in the class.captcha.php file called on there is nothing I see wrong, but I am an Amatuer..

<?php

class Text_Captcha
{
// Reference to the database connection where the `captchas` table resides
var $db_conn = false;

// Error message
var $err_msg = "";
//var $connection;
/**
* Constructs the class
* @param $db - Reference to the database connection where the `captchas` table resides
*/
function Text_Captcha(&$db = null) {
global $tryover,$sessid;
if (!empty($db)) $this->db_conn = $db;
if($tryover){
$this->sessid=$sessid;
}else
$this->sessid=session_id();
}

/**
* Randomly find one single question
*/
function get_question() {
global $tryover;
$ret = false;

if ($this->db_conn) {
// Count questions exist
$query = "SELECT COUNT(*) AS count_all FROM captchas";
$result = mysql_query($query, $this->db_conn);
$data = mysql_fetch_array($result);
$q_count = $data["count_all"];
mysql_free_result($result);

// Get one random row
$query = "SELECT * FROM captchas LIMIT " . (rand(0, $q_count-1)) . ", 1";
$result = mysql_query($query, $this->db_conn);
$data = mysql_fetch_array($result);
$ret = $data["question"];
$captcha_id = $data["capid"];
mysql_free_result($result);

// Persist captcha ID
$sessid=$this->sessid;
$this->persist_id($captcha_id,$sessid);
$rndkey=$this->setTempKey($captcha_id,$sessid);
if(!$rndkey)
$this->err_msg = "Key not posted";

} else {
$this->err_msg = "Can not find database connection";
}
$output=array($ret,$rndkey,$captcha_id);
return $output;
}


/**
* Validate user submitted CAPTCHA answer
* @param $answer - user answer
*/
function validate($answer,$capid,$rndkey) {

// $captcha_id = $this->persisted_id('id'); //this will not work over ajax;
$safeid=mysql_escape_string($captcha_id);
$sessid=$this->persisted_id('sess');
$answer_ok = false;

if (strlen($answer) > 0) {
if ($this->db_conn) {

$query = "
SELECT c.*, t.*
FROM captchas c, captcha_temp t
WHERE c.capid=t.capid
AND c.capid ='$capid'
AND t.rndkey='$rndkey'";


//echo "<br>$query<br>"; AND t.sessid='$sessid'";

$result = mysql_query($query, $this->db_conn);

if($result) {
$data = mysql_fetch_array($result);
$db_answer = $data["answer"];

if($db_answer){

// Match answer
$arr_answers = split(":", $db_answer);
for ($i = 0; $i < count($arr_answers); $i++) {
$check_against = $arr_answers[$i];
if (preg_match("/\b$check_against\b/i", $answer)) {
$answer_ok = true;
break;
}
}
}else{
$this->err_msg = "Empty answer";
}
}else{
$this->err_msg = "Question doesn't exist";
}
mysql_free_result($result);
}else{
$this->err_msg = "Can not find database connection";
}
}else{
$this->err_msg = "Empty answer";
}
//update the randkey with new vars
/* if(!$answer_ok){

$this->updateTempKey($capid);

} */
return $answer_ok;
}


/**
* Method for retrieving persisted TEMP ID
*
* @access private
*/

function deleteTempKey($capid, $sessid) {
global $database;

$table="captcha_temp";
$criteria="sessid='$sessid'";

$result =$database->deleteUser($table,$criteria);

if($result)
return $rndkey;
else
return false;
}



function setTempKey($capid,$sessid) {
global $session;
$rndkey=$session->generateRandID();

$query = "INSERT INTO captcha_temp (capid, sessid,`rndkey`) VALUES ($capid, '$sessid','$rndkey')";
$result =mysql_query($query, $this->db_conn);

if($result)
return $rndkey;
else
return false;
}

function updateTempKey($capid) {
global $database,$session;
$fvalue=$session->generateRandID();//$rndkey
$table="captcha_temp";
$fname="`rndkey`";
$idfield= 'capid';
$idval=$capid;

$result =$database->updateField($table,$fname,$fvalue,$idfield,$idval );
if($result) {
return $fvalue;
}else
return false;
}


/**
* Method for persiting currently showing CAPTCHA_ID
* This is only example, you should make use the server to persist this ID, instead of cookie in the client
* Normally you'd like to override this method to implement a safer mechanism,
* ie. database session variables storage, temp. table, etc.
*
* @param $id - CAPTCHA ID
* @access private
*/
function persist_id($id,$sessid){
$_SESSION["captcha_id"] = $id;
$_SESSION["sessid"] = $sessid;
}

/**
* Method for retrieving persisted CAPTCAH ID
*
* @access private
*/
function persisted_id($what){
if($what=='id'){
$out=$_SESSION["captcha_id"];
}else{
$out=$_SESSION["sessid"];
}
return $out;
}


}//end class

?>


Can anyone see anything or point me in a direction to follow as the files and forms are exactly the same as when they were
working on the previous hosting account..

Count a pathway have changed from the previous ' .. ' that some calls make or could have a field been corrupted? Or do I need to
do something to add the $ans_key field and hash somewhere in the SL DB as I can not find that?

Pulling the last of my hair out so I hope one of you Wizards can tell me what you see or if it could be something like a new hosting
company needing to check to ensure I am on a certain PHP scripting platform, etc...

Thanks in advance!!!

Jay

James Smith

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-31 : 13:39:58
I can't help you with PHP, but I can recommend against the procedure you used to transfer the database. Always use backup/restore or detach/attach methods.

Using the copy database wizard, import/export or any other procedure does not give you an exact copy. Backup/restore or detach/attach does.

Do you still have access to the old database to do a backup and then try a restore on the new server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jdsmith8
Starting Member

11 Posts

Posted - 2013-08-01 : 13:51:04
Thanks for that advice and I will use it in the future to avoid any issues.

Sorry for the delay.. I think I have found something that might be the issue.


I have the site folder under another account on Godaddy and the domain points to that folder using an "A Record" from where the domain is registered. I think that is why it does not recognize any answer for the form captcha due to it might not be picking up the correct pathway, but not sure if that counts as the start of the pathway since that is where it first lands.

So could the issue be as simple as a pathway?

I have mydomainname.com registered elsewhere and pointed to Godaddy on an account I have for MYCARDETAILING.com (EXAMPLE NAME), but under a folder /ATLANTALOCATION so when someone enters mydomainname.com it goes to pick up the A RECORD and lands on the Home Page inside the /ATLANTALOCATION folder in the Godaddy MYCARDETAILING.com account...

Basically I have not added a top level second hosting account with Godaddy yet.

So would the previous Database file have to have the pathway lines below changed somehow even though it starts from the sub-folder as that is where the incoming A RECORD lands?

//-------------------------------------------//FOLDER
//define("FULL_FOLDER_PATH", "http://localhost/junk/access/");
//define("ROOT_FOLDER_PATH", "http://localhost/junk/");
define("FULL_FOLDER_PATH", "http://mydomainname.com/access/");
define("ROOT_FOLDER_PATH", "http://mydomainname.com/");


I just do not know how the pathways work exactly when you land in a sub-folder and if the SQL DB files needs something changed.

I appreciate your help ... GREATLY!

Jay

James Smith
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-01 : 14:00:05
This doesn't at all sound like a SQL Server issue. I would suggest posting your question on a site that specializes in the technology where the problem is. We can only help with SQL Server here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jdsmith8
Starting Member

11 Posts

Posted - 2013-08-03 : 01:51:30
Thanks and it was corrected by actually changing the SQL DB Connection string to use DB_HOST and DB_PASSWORD as the script in the pages (PHP) was calling for DB_SERVER and DB_PASS and Godaddy used different tags for their connection string. Of course then I just had to go through the site files and change out the code snippets that had to deal with either of those calls.

Thanks for your help and the great advice about the Restore option...

That is the first time a SQL DB import was not working due to the main generated SQL CONNECTION STRING items for me so luckily that was the resolution...

Jay

James Smith
Go to Top of Page
   

- Advertisement -