Using $this->input->post in a SQL Query Update

[eluser]internut[/eluser]
Hey all. Quick question. Why would this work:

Code:

$this->db->query("UPDATE settings SET setting='$_POST[admin_name]' WHERE name='admin_name'");

and this does not:

Code:

$this->db->query("UPDATE settings SET setting='$this->input->post(admin_name)' WHERE name='admin_name'");

do i need to set the var above the query call?

$admin_name = $this->input->post(admin_name);

Can i just use $this->input->post(admin_name) in the query update?

[eluser]Derek Jones[/eluser]
You can't use functions inside strings. Only variables and class properties can be parsed by PHP in that context. Second, you aren't really using POST values directly in queries without escaping them are you?

[eluser]internut[/eluser]
No deff not going to do that. Just trying to figure out the best way I should get the post data and get it into the sql update query.

[eluser]Derek Jones[/eluser]
I personally like to create a new associative array of field names with values. It's cleaner in the code to me, and more explicit about what's being set to what, and encourages you to think about validation and security. Then simply:

Code:

$this->db->update('table_name', $data_array);

Let CI escape it for you. If you prefer manually written SQL, that's fine too, just make sure you use $this->db->escape() or $this->db->escape_str() as necessary. Either way, you'll need to either break out of the string to execute that, or do it before hand and set it to a variable.

[eluser]internut[/eluser]
Derek,

As you wrote this i was re-doing things and on the same page as you I believe. I did this:

Code:

$data = array(
                  'admin_name' => $this->input->post(admin_name),
                  'admin_email' => $this->input->post(admin_email),
                  'admin_username' => $this->input->post(admin_username),
                  'admin_password' => $this->input->post(admin_password),
                  'admin_url' => $this->input->post(admin_url),
                  'install_url' => $this->input->post(install_url)
               );

               $data = $this->db->escape_str($data);

               $this->db->query("UPDATE settings SET setting='$data[admin_name]' WHERE name='admin_name'");
               $this->db->query("UPDATE settings SET setting='$data[admin_email]' WHERE name='admin_email'");
               $this->db->query("UPDATE settings SET setting='$data[admin_username]' WHERE name='admin_username'");
               $this->db->query("UPDATE settings SET setting='$data[admin_url]' WHERE name='admin_url'");
               $this->db->query("UPDATE settings SET setting='$data[install_url]' WHERE name='install_url'");

               $this->session->set_flashdata('sys_message', 'General Settings:  Updated');  // set system message

Look ok?

wonder if i shoulduse the db update of CI?

[eluser]Derek Jones[/eluser]
I think update() is clearer and more concise, but when I write manual queries, I break out of the string to use escape_str() so no matter what changes might be made down the road to the code surrounding the query, you can know by looking at it whether or not the PHP variable being used has been properly escaped.

Complete aside, but wouldn't that schema be simpler if instead of multiple rows with different 'name' values you had a single row with columns for admin_name, admin_email, etc.?

[eluser]internut[/eluser]
Thanks for the help. Yeah i've done it this backwards way i guess you can call it for a while. Was comfortable with it. Going to change it to a single row setup.