Experts Round Table Network

"SELECT * FROM FXRATE" works as expected. But

$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3'";

failes to return anything. If i run the query  replacing the variable names with actual data it works.

thanks.

You are using PHP?

yes its php

Sorry, duh.

I only use parameterized SQL statements not inline SQL, so I confused myself. (also known as PREPARED statements in the PHP/MySQL world.)

"SELECT * FROM fxrate WHERE transcurcode = '" . $param2 . "' AND recevcurcode = '". $param3 ."'";

Try that.

And assuming
$query = "SELECT * FROM fxrate WHERE transcurcode = '" . $param2 . "' AND recevcurcode = '". $param3 ."'";
$result =  $query;

if (!$result) {
     $message  = "My Invalid query: " . mysql_error() . "<br>\n";
     $message .= "My Whole query: " . $query . "P2=" .$param2. "  P3=". $param3 . "<br>\n";
     die($message);
}

To see that the query is built correctly and that the parameters have the correct data or any data.

thanks Rob. can you link me to any good read on MYSQL?PHP prepered statement?

in fact, prepared statements are a benefit when you've repeated execution of the same statements. It's also an obligation when using Oracle, because Oracle is inefficient. Prepared statements have an overhead attached to them. Inline statements are faster to execute the first time. Prepared statements have NO MEANING when the RDBMS has a query cache ;-)

MySql has a wonderful query cache, so don't use prepared statements and you'llreach maximum efficiency :D

it's as simple as that IMHO

your query failed probably because you enclosed it in single quotes. You've to understand PHP expands variables only between double quotes or in an echo<<<MARKER...MARKER; statement (the most convenient type)

the best sugegstion I can do to you is to echo the query just the line before execution, after preparing it.
if you don't want to echo it, then echo it in an HTML comment.

for instance :

if the query doesn't FAIL but does not return any dataset either, you've to understand WHY :
or
(new lines will be put in the HTML source so that you may find the html comment right on start of a line)

regards

ok, re-reading your original posting, I have two explanations why your - correctly written AFAIK - query didn't work as expected :
- (least probable) variables don't hold the value you think they do
- (most probable) variables do contain non-printable characters like EOLN and consorts [thus it's the same reason as above]

try to output strlen($variable)."'".htmlspecialchars($variable)."'" and check out precisely
usually, the variable appears as 2 characters longer than it should : you've a \r\n embedded in it, that screws up your query.
classical issue.

I just outputed the strlen and the variable it self and its ok. :(

oookayyy

use one of the two "echo query" methods exposed above, copy-paste the query between the single quotes AS IS and paste/execute it in mysql console (type "mysql" in so-called "DOS" console, in \mysql_install\bin\ if you didn't set the PATH ))

it should FAIL (add a trailing semicolon, of course) but in case it does work check :
- results
- warnings (do "show warnings")

analyze and come back here with feedback

VGR regarding the echo query you are refering to should i replace the variables. If so how do they compare to those in my original post?

cheers and enjoy yourv weekend

no. Leave your variables. The purpose of the "echo" statements(³) is to see&check if the query is ok with the variables expanded ;-)

just do :

(³) there are other possibilities : database logging, textfile logging, logging to a specially crafted DIV, SPAN or window via DOM/AJAX, etc

below is what i got

Language: php (GeSHi-highlighted)
q='SELECT * FROM fxrate WHERE transcurcode = '' AND recevcurcode = '''<br>

meaning the variables are empty. This is strange because when i echo "strlen($variable)."'".htmlspecialchars($variable)."'", as oyu suggested earlier i get (3'GBP') for param2 and (3'GHC') for param3 which is correct. :(

thanks for your help so far.

But to the contrary...they have a very real meaning in a web environment, where one should never run in-line SQL statements which have any data which is not hard coded, regardless of whether is has been filtered prior or not.

The parameters of the prepared statement are not treated as SQL.  When you build and execute inline SQL, as is being done here, the variables passed to the SQL are injection vectors.

yes, that's the only purpose they're good at on a production system :D

I hate prepared statements. They are old Oracle time stuff.

thepreacher, show me the entire block of code where you populate the two variables (displace the statements if appropriate) and issue the query. there must be ***some problem somewhere *** ;-)

Ok please find below the code you requested

Language: javascript (GeSHi-highlighted)
		
var transcode = document.fxratescr.transcurcode.value;
		var recvcode = document.fxratescr.recevcurcode.value;
		
			getRequestobject();
				objRequest.onreadystatechange = handleHttpResponse2;
				var url = 'fetchfxData.php?param1=fxrate';
				objRequest.open("GET", url+'&param2='+transcode+'&param3='+recvcode, true);
				objRequest.send(null);
			
						function handleHttpResponse2() {
			if (objRequest.readyState == 4) {
		        if (objRequest.status == 200) {
					//var results2 = eval("(" + objRequest.responseText + ")");
					var results2 = objRequest.responseText;
					//alert(results2.messages.message[0].dateset);
		            alert(results2);
		            objRequest=null;
					document.fxratescr.dateset.value = results2.messages.message[0].dateset;
					document.fxratescr.transcurcode.value = results2.messages.message[0].transcurcode;
					document.fxratescr.recevcurcode.value = results2.messages.message[0].recevcurcode;
					document.fxratescr.midrate.value = results2.messages.message[0].midrate;
					document.fxratescr.periodtype.value = results2.messages.message[0].periodtype;

and the PHP is

Language: php (GeSHi-highlighted)
$param1 = $_GET["param1"]; //tablename
$param2 = $_GET["param2"];
$param3 = $_GET["param3"];
 
	$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3'";
  $json = '{"messages": {';
  if ($count > 0) 
	{	
		$json .= '"message":[ ';
		while ($row = mysql_fetch_array($result)) 
		{
		$json .= '{';
		$json .= '"dateset": "' . htmlspecialchars($row['dateset']) . '",
					"transcurcode": "' . htmlspecialchars($row['transcurcode']) . '",
					"recevcurcode": "' . htmlspecialchars($row['recevcurcode']) . '",
					"midrate": "' . htmlspecialchars($row['midrate']) . '",
					"periodtype": "' . htmlspecialchars($row['periodtype']) . '",
					"fromdate": "' . htmlspecialchars($row['fromdate']) . '",
					"todate": "' . htmlspecialchars($row['todate']) . '",
				},';	
	    }
        $json=substr($json,0,-1); //VGR REM get rid of the last comma ;-)))
		$json .= ']';
	}
	$json .= '}}';
	echo $json;

thanks

well, I don't know if $count is set, but I can tell you there is something BAD happening to $param1, $param2 and $param3 between the point where you set them to $_GET equivalents, and the point qhere you build the $query, because this proves it works just fine :

http://www.fecj.org/extra/test_preacher.php?param1=tablename&param2=pa_2_value&param3=par_3_value (http://www.fecj.org/extra/test_preacher.php?param1=tablename&param2=pa_2_value&param3=par_3_value)

that's the test code :

BTW, your comment :

$param1 = $_GET['param1']; //tablename

makes me wonder whether $param1 should replace the litteral 'fxrate" in the query, or not. Up to you.

I'm quite sure that query for HTML issues - like  add or strip slashes, double quotes and the nastiest as mentioned htmlspecialchars() - so eventually when U output syntax to screen and run it at console or what ever your MySQL tool is everything works fine but will constantly fails at real run - and may drive you crazy sometimes ;-).

M.D.

yes, but as I provided a very basic code for testing, he can see if th ebehaviour is different on his computer (server) than on mine.
usually the problems arise from magic_quoting or urlencoding. I doubt he passes any non-ASCII-7bits characters in the param1, param2 and param3 (those being mysql settings like tablenames, and row names ;-) but as far as magic_quoting occurs... he hs to test.
moreover, he uses  a GET method call, so magic quoting shouldn't be a problem theoretically.
if the sample code I provided does what it has to do on your server, just add the mysql_query($query) call afterwards... AND IT SHOULD WORK.

if not, come back with more feedback.

Thanks VGR, Rob and all. VGR your code worked and am now a happy man. BTW VGR can you  please elaborate on why you changed the " in the

Language: php (GeSHi-highlighted)
$param3 = $_GET["param3"];

to

Language: php (GeSHi-highlighted)
$param3 = $_GET['param3']; //VGR REM fixed idiotic double quotes 
 

also is it conventional the addition of the trailing semicolon to the quesry statement :

Language: php (GeSHi-highlighted)
$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3';"; //VGR REM added trailing semicolon (SQL terminator)
echo "q='$query'<br>";

Thanks once again

fast answer :

1) the actual string litteral delimitor in SQL AND in PHP is the single quote.
example 'this is a test phrase'

PHP admits two other ways (and perhaps more, see "types and constants" and "expressions" in PHP manual, top sections at www.php.net/manual/en/ )
"this is a $variable phrase" which will produce the same result if echoed if you state ***beforehand*** that  (note the single quotes)

other possibility (my favourite, also to be found at the forementioned web pages)
echo<<<MARKER
this is a $variable phrase<br>
and a very<br>
long string that can span an entire script<br><hr>
that's the way I transform a HTML static page to a PHP dynamic one because <br>
replacing variables by their $values is easy even for array, matrix or session/get/post {$_SESSION['somevar']}<br>
the onyl bad case are the constants that can not be "expanded"
you can mix 'all' kind of "quotes" or escaped stuff\"toto"youpie\" in there.
MARKER;

MARKER can be almost any string you like ; usually rather shorter : EOS (end-of-string), EOR (repeat), BB, EOT (table) those are just mnemonics)

So my comment about "idiotic" means that :
- single quotes are fast. they don't enable "variable expansion" (or "replacement with values")
- double quotes do allow such expansion, and are thus slower.
- using double quotes with a litteral (ie, no variables to expand) is idiotic.
ok ?

single quotes ar ethe rule, double are the exception.

2) single quote is THE string delimitor in SQL, and the ONLYone. SQL is a standardized ISO language. You've no choice.