INSERT INTO

[turnin]

Okay,

I'm hoping one of you can help me.

I have a mysql database that I have configured through phpmyadmin.

I have an android app that simply makes and sends a mysql query

I can get it to successfully return values when using Select statements but when I use INSERT INTO, it returns " Error Query is invalid" BUT BUT BUT, when I use the same string and enter it through the sql tab in myphpadmin it works fine !

 

So here is the string ( the semicolons at the end of each field name are so I can use something common to split the string up when the data arrives back on the phone)

randomkey||||||INSERT INTO table4  (`geolat;` , `geolong;` , `mode;` , `destgeolat;` , `destgeolong;` , `cellphone;` , `email;` , `carrego;` , `colour;` ,  `rating;` , `comment;`)
VALUES (0.0,0.0,'driver' ,-43.54779,172.62472, , '' ,'' , 'text' , 'ratingleftblank' , 'commentblank' )

the index4.php script is as follows

?php
/*
* Written By: 
* James
*/


/************************************CONFIG****************************************/
//DATABSE DETAILS//
$DB_ADDRESS="mysql1.openhost.net.nz";
$DB_USER="bling44";
$DB_PASS="sadlyinept";
$DB_NAME="bling44";

//SETTINGS//
//This code is something you set in the APP so random people cant use it.
$SQLKEY="randomkey";
/************************************CONFIG****************************************/

//these are just in case setting headers forcing it to always expire and the content type to JSON
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');


if(isset($_POST['tag'])){	//checks ifthe tag post is there
$tag=$_POST['tag'];
$data=explode("||||||",$tag);	//split the SQL statement from the SQLKEY
if($data[0]==$SQLKEY){		///validate the SQL key
	$query=$data[1];
	$link = mysql_connect($DB_ADDRESS,$DB_USER,$DB_PASS);	//connect ot the MYSQL database
	mysql_select_db($DB_NAME,$link);			//connect to the right DB
	if($link){
		$result=mysql_query($query);			//runs the posted query (NO PROTECTION FROM INJECTION HERE)
		if($result){
			if (strlen(stristr($query,"SELECT"))>0) {	//tests if its a select statemnet
				$outputdata=array();
				while ($row = mysql_fetch_assoc($result)){
					$outputdata[]=$row;		//formats the result set to a valid array
				}
				echo json_encode(array("VALUE",$tag,array_merge($outputdata)));	//sends out a JSON result with merged output data
			} else {
				echo json_encode(array("VALUE",$tag,array_merge(array(array("AFFECTED_ROWS ".mysql_affected_rows($link)))))); //if the query is anything but a SELECT it will return the array event count
			}
		} else echo json_encode(array("VALUE",$tag,array_merge(array(array("ERROR QUERY IS INVALID"))))); //errors if the query is bad
		mysql_close($link);	//close the DB
	} else echo json_encode(array("VALUE",$tag,array_merge(array(array("ERROR Database Connection Failed"))))); //reports a DB connection failure

} else {
	echo json_encode(array("VALUE",$tag,array_merge(array(array("ERROR BAD CODE SUPPLIED")))));	//reports if the code is bad
}
}
?>

So to reiterate.

I can search the DB but can't INSERT INTO, unless I go through the myphpadmin interface.

Any ideas are very much appreciated

[turnin]

My version phpadmin is ver 3.3.1

Don't know how to find the mysql version number ?

[kickstart]

Hi

 

You appear to be trying to insert nothing into `cellphone;` rather than NULL.

 

All the best

 

Keith

[turnin]

ok, yes, thanks Kickstart.

I can now insert, but only fields with numeric values, as soon as I insert to a text or varchar field I get errors. but again, I don't get the error if I go through the myphpadmin interface. I've tried using single , double and no quotes and I'm changing the data types field by field, still nothing. why would it be different using the phpmyadmin interface, The only thing I'm doing is deleting is is the  "key|||||" part before the actual statement

[kickstart]

Hi

 

Can you echo out the SQL that is failing and paste it here?

 

All the best

 

Keith

[PFMaBiSmAd]

You could also use mysql_error() to get php/mysql to tell you why the query is failing.

[turnin]

key||||||INSERT INTO table4  (`geolat;` , `geolong;` , `mode;`)    VALUES (0.0, 0.0, "passenger")

 

I know when I use INSERT INTO with just geolat and geolong I have success, as soon as I put mode; in there , it fails.

 

Not sure how to use mysql_error() just yet, does this go into the index.php script ?

[kickstart]

Hi

 

For now try

 

$result=mysql_query($query) or die(mysql_error());        //runs the posted query (NO PROTECTION FROM INJECTION HERE)

 

All the best

 

Keith

[turnin]

A JSON array text must start with '[' at character 1 of You have an error in your SQL syntax; check the manual..................syntax to use near '\'driver\') at line 1

 

this came from sending

randomkey||||||INSERT INTO table4  (`geolat;` , `geolong;` , `mode;`)    VALUES (0.0,0.0,'driver')

 

[PFMaBiSmAd]

The problem is because php.net thought they would -

help a few beginners blissfully and unknowingly write better (more secure) code.

 

Above quote is from the magic_quotes section of the php.net documentation.

[turnin]

hmmm, OK, I've had a brief read, I assume I need to turn off magic quotes ?

How can I achieve this ?

Thanks for all the help so far