How to pass MySQL database connection data in namevaluepairs from Android to PHP

Question

I am trying to connect to MySQL using PHP, passing the database connection parameters from Android. I don't want to hardcode the connection parameters, and don't want to store them in a separate file. My code worked fine when I had the database parameters in the PHP, but doesn't work now that I try to pass them from Java to PHP with namevalue pairs as below.

Nothing has changed except for the PHP connection using passed variables instead of being hardcoded, so I suspect some formatting or REGEX issue, but can't find any solution. Any help greatly appreciated!

Problem(s) solved per assistance from VolkerK. See original PHP code and updated underneath.

ORIGINAL SQLQuery.php:

<?php
mysql_connect($_REQUEST['url'],$_REQUEST['username'],$_REQUEST['password']);
mysql_select_db($_REQUEST['database']);
$q=mysql_query($_REQUEST['SQL']);
while($e=mysql_fetch_assoc($q))
        $output[]=$e;
print(json_encode($output));
mysql_close();
?>

WORKING SQLQuery.php:

<?php
if (get_magic_quotes_gpc()) {
    $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
    while (list($key, $val) = each($process)) {
        foreach ($val as $k => $v) {
            unset($process[$key][$k]);
            if (is_array($v)) {
                $process[$key][stripslashes($k)] = $v;
                $process[] = &$process[$key][stripslashes($k)];
            } else {
                $process[$key][stripslashes($k)] = stripslashes($v);
            }
        }
    }
        unset($process);
}

define('DEBUGLOG', true); 
$output = array(); 

$mysql = mysql_connect($_REQUEST['url'],$_REQUEST['username'],$_REQUEST['password']); 
if ( !$mysql ) { 
    $output['status']='Error'; 
    $output['errormsg']='MySQL connect error'; 
    if ( defined('DEBUGLOG') && DEBUGLOG ) { 
        $output['errordetails'] = array( 
            'msg'=>mysql_error(), 
            'url'=>$_REQUEST['url'], 
            'username'=>$_REQUEST['username'], 
            'password'=>$_REQUEST['password'] 
        ); 
    } 
} 
else if ( !mysql_select_db($_REQUEST['database']) ) { 
    $output['status']='Error'; 
    $output['errormsg']='Database select error'; 
    if ( defined('DEBUGLOG') && DEBUGLOG ) { 
        $output['errordetails'] = array( 
            'msg'=>mysql_error(), 
            'url'=>$_REQUEST['url'], 
            'database'=>$_REQUEST['database'] 
        ); 
    } 
} 
else if ( false===($q=mysql_query($_REQUEST['SQL'])) ) { 
    $output['status']='Error'; 
    $output['errormsg']='Query error'; 
    if ( defined('DEBUGLOG') && DEBUGLOG ) { 
        $output['errordetails'] = array( 
            'msg'=>mysql_error(), 
            'url'=>$_REQUEST['url'], 
            'SQL'=>$_REQUEST['SQL'] 
        ); 
    } 
} 
else { 
    while( $e=mysql_fetch_assoc($q) ) { 
        $output[]=$e; 
    } 
} 

print(json_encode($output));

Extract from my Android Code (details changed to protect the innocent!):

String phpDBURL = "mysqlserver.blah.com:3306";
String phpURL = "http://www.blah.com/php/";
String dbname ="dbref_Evaluate";
String username = "dbref_admin";
String password = "password";
String SQL = "SELECT ID, ShortDesc FROM User WHERE Account = '[email protected]'";
//the query to send
ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
nameValuePairs.add(new BasicNameValuePair("url",phpDBURL));
nameValuePairs.add(new BasicNameValuePair("username",username));
nameValuePairs.add(new BasicNameValuePair("password",password));
nameValuePairs.add(new BasicNameValuePair("database",dbname));
nameValuePairs.add(new BasicNameValuePair("SQL",SQL));
Log.v("Common.SQLQuery", "Passing parameters: " + nameValuePairs.toString());
//http post
try{
        HttpClient httpclient = new DefaultHttpClient();
        HttpPost httppost = new HttpPost(phpURL + "SQLQuery.php");
        httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        HttpResponse response = httpclient.execute(httppost); 
        HttpEntity entity = response.getEntity();
        InputStream is = entity.getContent();
//convert response to string

etc.

mysql_ is going to be deprecated and removed from PHP, php.net/manual/en/…. Learn to use PDO instead anuary.com/54/… — Gajus
– Gajus, Commented Aug 2, 2012 at 12:37

score 1 · Accepted Answer · 2012-08-02 10:14:43Z

1

use POST instead of REQUEST in the php file

edited Aug 2, 2012 at 10:14

answered Aug 2, 2012 at 10:10

user1567667

Sign up to request clarification or add additional context in comments.

4 Comments

user1567667 Over a year ago

did u change the request to post

user1570952 Over a year ago

I tried this, but it made no difference. I had REQUEST in for the query and it worked, so used REQUEST when I added the database parameters assuming this should also get these variables passed (incorrect logic??).

user1570952 Over a year ago

To be clear: tried changing to POST for the mysql_connect -> no impact. Tried changing to POST for the mysql_select_db -> combined changes no impact. For good measure, tried also changing to POST for the mysql_query, and no impact.

user1570952 Over a year ago

see the error reported attached in a reply to the answer below - thanks

VolkerK · Accepted Answer · 2012-08-02 10:12:38Z

0

You need ~~more~~ error handling. Any of the mysql_* function can fail and your code has to react on that.

Crude example:

<?php
define('DEBUGLOG', true);
$output = array();

$mysql = mysql_connect($_REQUEST['url'],$_REQUEST['username'],$_REQUEST['password']);
if ( !$mysql ) {
    $output['status']='error';
    $output['errormsg']='database error';
    if ( defined('DEBUGLOG') && DEBUGLOG ) {
        $output['errordetails'] = array(
            'msg'=>mysql_error(),
            'url'=>$_REQUEST['url'],
            'username'=>$_REQUEST['username'],
            'password'=>$_REQUEST['password']
        );
    }
}
else if ( !mysql_select_db($_REQUEST['database']) ) {
    $output['status']='error';
    $output['errormsg']='database error';
    if ( defined('DEBUGLOG') && DEBUGLOG ) {
        $output['errordetails'] = array(
            'msg'=>mysql_error(),
            'url'=>$_REQUEST['url'],
            'database'=>$_REQUEST['database']
        );
    }
}
else if ( false===($q=mysql_query($_REQUEST['SQL'])) ) {
    $output['status']='error';
    $output['errormsg']='database error';
    if ( defined('DEBUGLOG') && DEBUGLOG ) {
        $output['errordetails'] = array(
            'msg'=>mysql_error(),
            'url'=>$_REQUEST['url'],
            'SQL'=>$_REQUEST['SQL']
        );
    }
}
else {
    while( $e=mysql_fetch_assoc($q) ) {
        $output[]=$e;
    }
}

print(json_encode($output));

answered Aug 2, 2012 at 10:12

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

5 Comments

user1570952 Over a year ago

Thanks for such a comprehensive reply. I put this error handling in, and bizarrely it points to an error in the one piece of PHP/Java/SQL I did NOT change! I get the following error response:

user1570952 Over a year ago

result: {"status":"error","errormsg":"database error","errordetails":{"msg":"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 '\\'[email protected]\\'' at line 1","url":"mysql.blah.com:3306","SQL":"SELECT ID, Name FROM User WHERE Account = \\'[email protected]\\'"}}

user1570952 Over a year ago

The error has encoded the e-mail / account address passed in the SQL. I assume that the namevaluepair did not encode this as MySQL would clearly have a problem with it. So I have no idea what is the source of the syntax error.

VolkerK Over a year ago

Maybe your php server has those stupid magic quotes turned on, see docs.php.net/magic_quotes

user1570952 Over a year ago

Excellent, good catch. Don't know why this didn't cause a problem previously, but it works now by adding the code to disable the magic quotes. I will update the PHP code above to reflect this. Thanks for the help.

Collectives™ on Stack Overflow

How to pass MySQL database connection data in namevaluepairs from Android to PHP

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related