HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Unable to connect to database via Live Look up script


#1

Hello Everyone,

I have created the following livelookup.php script inside the http://www.hostname/custom_code/ directory:

<?php /***** DATABASE CONNECTION VARIABLES *****/ $host = "x.y.z.w,1433"; $user = "sa"; $pswd = "sa****"; $name = "Portal_DB"; //database name /***** MSSQL HELPER FUNCTION *****/ function mssql_escape($string){ if(get_magic_quotes_gpc()){ return $string; }else{ return addslashes($string); } } //Defining Live Look up Variables $cust_id_helpspot = $_GET['customer_id']; $cust_email_helpspot = $_GET['email']; $cust_companyname_helpspot = $_GET['company_name']; $cust_firstname_helpspot = $_GET['first_name']; $cust_lastname_helpspot = $_GET['last_name']; $cust_phone_helpspot = $_GET['phone']; /***** QUERY DATABASE *****/ $link=mssql_connect($host,$user,$pswd); if(!is_resource($link)){ echo 'Unable to connect to database'; }else{ //Select the database to query // mssql_select_db($name, $link); - Tried Did not work //mssql_select_db('[Portal_DB]', $link); - Tried Did not work mssql_select_db('['.$name.']', $link); //Query the db $my_query = "SELECT * FROM dbo.customers WHERE customer_id = ".$cust_id_helpspot." AND email = ".$cust_email_helpspot.""); $result = mssql_query($my_query); } /***** OUTPUT LIVE LOOKUP XML *****/ header('Content-type: text/xml'); echo '<?xml version="1.0" encoding="ISO-8859-1"?>';

?>

<?php while($row = mssql_fetch_assoc($result)): ?>

<customer_id><?php echo $row['customer_id']; ?></customer_id>
<first_name><?php echo $row['first_name']; ?></first_name>
<last_name><?php echo $row['last_name']; ?></last_name>
<?php echo $row['email']; ?>
<?php echo $row['phone']; ?>


<?php endwhile; ?>

I have enabled Live Lookup with HTTPvia GET option and Path to Script contains: http://x.y.z.w/custom_code/livelookup.php

When I send a ticket to the helpspot using the helpspot widget that I embedded on one of my web application entering email and first and last name (that match the entries in my customers table on the DB) and click on LiveLookup, I get ‘Customer not found’ error.

Kindly suggest what am I doing wrong here.

FYI, I am Currently Running Version: 2.7.2 and I have a trial license. And I am relatively new to php and deploying webservers.

Any suggestions or pointers would be useful.

Thanks.
Rohan


#2

Hi Rohan,

The problem here is that your query is using AND so it’s checking for the ID AND email. You probably want to check ID OR email.

Also to this script where you build the query you should wrap the PHP variables in the where query with the PHP addslashes() function to prevent SQL injection.


#3

Hi Ian,

Thank you so much for getting back. I fixed both the things you mentioned above.

  1. $my_query = “SELECT * FROM dbo.customers WHERE email = “.$cust_email_helpspot.””;
  2. cust_id_helpspot = mssql_escape(_GET[‘customer_id’]);
    cust_email_helpspot = mssql_escape(_GET[‘email’]); … etc
    For preventing SQL Injection.

But LiveLook up still doesn’t work and it still returns ‘customer not found’. I also checked logs and I do not see any error there as well. Can you tell me possible reasons for the failure.

Thanks.


#4

Try removing dbo. from dbo.customers it should not be needed. Also if that doesn’t work try removing the where clause completely. You should get a list of everyone in that way.

If none of that works try working with the script directly and not through HelpSpot. That is useful to be able to tweak and check. So http://x.y.z.w/custom_code/livelookup.php?email=email@me.com right in your browser and then view source and check the XML generated.