Welcome Guest, Not a member yet? Register   Sign In
convert value from routes/uri to string
#1

Controller
PHP Code:
public function verification($activation$email)
{
    
$user $this->model->user('user_activation'$activation);


Model
PHP Code:
public function user($key$value)
{
 
   return $this->db->query("
       SELECT *
         FROM users
         WHERE 
$key = $value // The problem here
     "
)->row();



The error message is directed to $ value, the value of $ activation is taken from routes / url.
I try to use single quotes so that it becomes '$ value'. and the results are correct.
my question is whether using single quotes doesn't matter? or is there a better way?
Reply
#2

It's easier with Query Builder syntax:

PHP Code:
public function user($key$value)
{
   return 
$this->db->where($key,$value)->get('users')->row();

Reply
#3

(04-13-2019, 02:13 AM)Wouter60 Wrote: It's easier with Query Builder syntax:

PHP Code:
public function user($key$value)
{
 
  return $this->db->where($key,$value)->get('users')->row();


Previously thanks for the answer, I know and before I tried using the query builder and it worked.
but the query builder has restrictions (in my opinion). therefore I used a manual query because of its flexibility and I decided to always use manual queries.

But I will try to understand the query builder to be more flexible.
Reply
#4

You can also try query bindings:

PHP Code:
$sql "SELECT * FROM users WHERE $key = ?";
$this->db->query($sql, array($value)); 
Reply
#5

(04-13-2019, 03:04 AM)Wouter60 Wrote: You can also try query bindings:

PHP Code:
$sql "SELECT * FROM users WHERE $key = ?";
$this->db->query($sql, array($value)); 

it doesn't work
Reply
#6

(This post was last modified: 04-14-2019, 07:03 AM by dave friend.)

(04-14-2019, 05:48 AM)DELE Wrote:
(04-13-2019, 03:04 AM)Wouter60 Wrote: You can also try query bindings:

PHP Code:
$sql "SELECT * FROM users WHERE $key = ?";
$this->db->query($sql, array($value)); 

it doesn't work

Did you do this?
It is likely what Wouter60 was implying.

PHP Code:
public function user($key$value)
{
    
$sql "SELECT * FROM users WHERE $key = ?";
    
$query $this->db->query($sql, array($value));
    return 
$query->row();


The double quotes to define the query string are required.

Use of query binding is highly desirable for the security it provides.
Reply
#7

(04-14-2019, 07:01 AM)dave friend Wrote: Did you do this?
It is likely what Wouter60 was implying.

PHP Code:
public function user($key$value)
{
 
   $sql "SELECT * FROM users WHERE $key = ?";
 
   $query $this->db->query($sql, array($value));
 
   return $query->row();


The double quotes to define the query string are required.

Use of query binding is highly desirable for the security it provides.

I know and I have tried it and failed if I use the method above. the error is only caused because the value of value is not string type. if I use a basic query I am not happy to see $value flanked by single quotes, if I use an automatic query builder consider the value of $value to be a string but the disadvantage (in my opinion) if using this method is less flexibility.

if you are willing to help me to change it to a query builder I am very happy.
PHP Code:
   public function user_campaign($key$value)
 
   {
 
       return $this->db->query("
            SELECT *
            FROM (
                SELECT *
                FROM users
                WHERE 
$key = $value
            ) users_campaigns
            INNER JOIN campaigns
            ON users_campaigns.id = campaigns.id
        "
)->row();
 
   
Reply
#8

(04-15-2019, 06:26 PM)DELE Wrote: I know and I have tried it and failed if I use the method above. the error is only caused because the value of value is not string type. if I use a basic query I am not happy to see $value flanked by single quotes, if I use an automatic query builder consider the value of $value to be a string but the disadvantage (in my opinion) if using this method is less flexibility.

In my testing, it does not matter if $value is a string or an integer. Nor does it matter if the string is flanked with single quotes or not.
My test set up has the $key field of type int. I can set $value to a digit or a string representation of that digit and the correct record is returned.

In other words, using this code
PHP Code:
$sql "SELECT * FROM users WHERE $key = ?";
$query $this->db->query($sql, array($value)); 

I get the same results from either of these

PHP Code:
$value 307;
$value '307'

Are using some other non-string data type for the $key field?
Reply
#9

(04-15-2019, 06:26 PM)DELE Wrote: if you are willing to help me to change it to a query builder I am very happy.
PHP Code:
   public function user_campaign($key$value)
 
   {
 
       return $this->db->query("
            SELECT *
            FROM (
                SELECT *
                FROM users
                WHERE 
$key = $value
            ) users_campaigns
            INNER JOIN campaigns
            ON users_campaigns.id = campaigns.id
        "
)->row();
 
   

I don't see why you are using the sub-select. Here is how to do the above using Query Builder, but without the sub-select.

PHP Code:
   public function user_campaign($key$value)
 
   {
 
       return $this->db
            
->where($key$value)
 
           ->join('campaigns''users_campaigns.id = campaigns.id''inner')
 
           ->get('users')
 
           ->row();
 
   
Reply
#10

(04-16-2019, 07:21 AM)dave friend Wrote: I don't see why you are using the sub-select. Here is how to do the above using Query Builder, but without the sub-select.

PHP Code:
   public function user_campaign($key$value)
 
   {
 
       return $this->db
            
->where($key$value)
 
           ->join('campaigns''users_campaigns.id = campaigns.id''inner')
 
           ->get('users')
 
           ->row();
 
   


I think in basic queries it is required to select in sub-queries, am I correct?

I have used the logic above before but with basic queries that are required to use sub-select so that it gets erorr which is caused to clash in the ID column, that's why I rotate that logic.


the above method does produce the data that I want. but the method above is not a sub-query.
the concern is that in the future I have to use multiple sub-queries.

what if I faced a query like this (this is only for example):
PHP Code:
public function user_campaign($var1$var2$var3$var4 )
{
 
   return $this->db->query("
        SELECT *
        FROM (
            SELECT *
            FROM users
            WHERE 
$var1 = '$var2'
        ) a
        INNER JOIN (
            SELECT *
            FROM campaigns
            WHERE 
$var3 = '$var3'
        ) b
        ON a.id = b.id
    "
)->row();


I apologize in advance if I bother you
Reply




Theme © iAndrew 2016 - Forum software by © MyBB