CodeIgniter Forums

Full Version: Nothing returned when using SQL query builder
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi,

This topic follow the now-closed issue #4614 on Github.

Here is the problem :

Quote:I'm pretty new to Code Igniter, but something does bother me ... it seems that some query built using query builder doesn't work.
I have a table (named table1 here), and I need to select data from it, considering name column.

// Returns nothing
$query = $this->db->get_where('table1', array('name' => strtoupper($this->db->escape($name))));

// Nothing here too
$sql = "SELECT * FROM table1 WHERE name = ?;";
$query = $this->db->query($sql, array(strtoupper($this->db->escape($name))));

// Return values
$sql_name = strtoupper($this->db->escape($name));
$sql = "SELECT * FROM table1 WHERE name = {$sql_name};";
$query = $this->db->query($sql);

// Values are used here
$data['json'] = json_encode($query->result_array(), JSON_UNESCAPED_UNICODE);


I'm using a Postgresql database.
I think I'm using code provided by the documentation in a correct way, but maybe I'm somewhere wrong.

I was invited to test the result of the last query with echo $this->db->last_query();

Here is the result :

PHP Code:
// The code
$query = $this->db->get_where('table1', array('name' => strtoupper($this->db->escape($name))));
echo 
$this->db->last_query();

// The result
SELECT *
FROM "table1"
WHERE "name" '''MYDATA''' 

Do you have any idea of what is wrong here ? Obviously, the query is not correct, but why is MYDATA under 3 ' ?
http://www.codeigniter.com/user_guide/da...ng-queries

Code:
$query = $this->db->get_where('table1', array('name' => strtoupper($this->db->escape_str($name))));
echo $this->db->last_query();
Query Builder - the values are escaped automatically by the system.
InsiteFX is correct. I wrote something stupid. So:

Code:
$query = $this->db->get_where('table1', array('name' => strtoupper($name)));
echo $this->db->last_query();
(05-06-2016, 07:29 AM)ivantcholakov Wrote: [ -> ]http://www.codeigniter.com/user_guide/da...ng-queries

Code:
$query = $this->db->get_where('table1', array('name' => strtoupper($this->db->escape_str($name))));
echo $this->db->last_query();

Thanks for your reply. Smile But what's the difference between $this->db->escape() and [b]$this->db->escape_str() ?[/b]
(05-06-2016, 01:28 PM)InsiteFX Wrote: [ -> ]Query Builder - the values are escaped automatically by the system.

Thanks for your reply.
So, the examples given in the documentation are 100% secure ?

And
$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array($_POST['id'], $_POST['status'], $_POST['author']));

is a correct way to do queries ?