...

View Full Version : SQL single quotes..... AHHHHHHH!



semiSkim
01-14-2013, 07:36 PM
I'm going slightly mad here.... the following SQL query executes fine in phpmyadmin yet when run through PHP throws an error!



SELECT * FROM _swoop_error_log WHERE ((string='2')) LIMIT 10 OFFSET 0



This code will run however:


SELECT * FROM _swoop_error_log WHERE ((string=2)) LIMIT 10 OFFSET 0



PHP:


echo($qry);
$result = $qry->Execute();


// ------- Execute method in my "QueryController" class
function Execute(){
if(@$this->result = Core::$connection->query($this->query)){
return $this->result;
}
Oops::Fatal("SQL ERROR: (" . Core::$connection->errno . ") " . Core::$connection->error);
return false;
}

Fou-Lu
01-14-2013, 08:35 PM
What error is it throwing? Sounds to me that you didn't sanitize it prior to sending it. The integer 2 works in MySQL regardless of if its a string or an integer so long as the strict datatype mode isn't enforced.
Can't tell you how to fix it really since this is using a completely custom connection. Possibly a MySQLi under the hood? If so, just use prepared statements.

semiSkim
01-14-2013, 08:53 PM
What error is it throwing? Sounds to me that you didn't sanitize it prior to sending it. The integer 2 works in MySQL regardless of if its a string or an integer so long as the strict datatype mode isn't enforced.
Can't tell you how to fix it really since this is using a completely custom connection. Possibly a MySQLi under the hood? If so, just use prepared statements.

Thanks for the input, I have started to narrow down the issue. The code in question is generating a table of displaying data. Included are selected boxes acting as filters. The table construct is sent to javascript via json which then modifies the object when the user changes something and makes an ajax request call with the new object.

THE ISSUE: When grabbing the data from JSON an extra 10 chars have appeared when performing a check with strlen:



$data = json_decode(html_entity_decode(Core::$post->data),true);
$table = new Table(-1,-1);

$table->settings = $data['settings'];
$table->perPage = $data['perPage'];
$table->withSelected = $data['withSelected'];
$table->searchTypes = $data['searchTypes'];

foreach($data['columns'] as $column){
$table->AddColumn($column['header'], $column['fields']);
}

foreach($data['filters'] as $key => $filter){
echo strlen($filter['selected']) . "----72 "; //SHOULD ECHO 72---72 BUT ECHOS 82----72
$table->AddFilter($filter['label'],$filter['values'],$filter['selected']);
}


echo $table->Render();

The data is url-decoded by the systems core.

semiSkim
01-14-2013, 09:14 PM
okay, problem found!!

Lesson of the day, never ever ever urlencode a JSON string in javascript...... I actually remember thinking at the time it probably doesn't need it.... but hey why not it can't hurt! ......how wrong could I be!?

Fou-Lu
01-14-2013, 09:42 PM
What makes it too long? Too much trailing space? Too much preceding space? Conveted entities? You'll need to figure out what is making it longer than expected.


Lols, glad you got it figured out. Jeez I was away for almost an hour? Awesome.
You should be safe using encoding on the *data* you are giving to the JSON, but it likely wouldn't be necessary. Never (for lack of a better term) molest the data after its been converted by something like json_encode. That's like changing the data directly given during a pack command and hoping you can read it out.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum