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.

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.

[font=Consolas, 'Liberation Mono', Menlo, Courier, monospace]// 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 [font=Consolas, 'Liberation Mono', Menlo, Courier, monospace]echo $this->db->last_query();[/font]

Here is the result :

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

// The result
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 ' ?

$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:

$query = $this->db->get_where('table1', array('name' => strtoupper($name)));
echo $this->db->last_query();
(05-06-2016, 07:29 AM)ivantcholakov Wrote: [ -> ]

$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][font=Lato, proxima-nova, 'Helvetica Neue', Arial, sans-serif]$this->db->escape_str() ?[/font][/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 ?

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array([font=Consolas, 'Andale Mono WT', 'Andale Mono', 'Lucida Console', 'Lucida Sans Typewriter', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Liberation Mono', 'Nimbus Mono L', Monaco, 'Courier New', Courier, monospace]$_POST['id']
, $_POST['status'], $_POST['author']));[/font]

is a correct way to do queries ?