Welcome Guest, Not a member yet? Register   Sign In
prepared statements
#1

(This post was last modified: 05-24-2020, 02:49 PM by joseCarlos.)

Hi again.



So I was trying to improve safety in my project by using prepared statements, and it just doesn't seems to work.

would you be so kind as to take a look and see if you find anything out of place?



this is the model function

 

PHP Code:
public static function insert_contactos($data_array)
  {
    $db db_connect();

    $pQuery $db->prepare(function ($db) {
      return $db->table('contactos')
        ->insert([
          'ativo_contacto' => '',
          'nome_contacto' => '',
          'cod_cliente_contacto' => ''
        ]);
    });

    $result $pQuery->execute(
      $data_array['ativo_contacto'],
      $data_array['nome_contacto'],
      $data_array['cod_cliente_contacto']
    );

    return $result;
  

This is the controller function
PHP Code:
  public function insert()
  {
    if ($this->request->isAJAX()) {


      $data_array['ativo_contacto'] = $_POST['ativo_contacto'];
      $data_array['nome_contacto'] = $_POST['nome_contacto'];
      $data_array['cod_cliente_contacto'] = $_POST['cod_cliente_contacto'];


      if (Contactos_model::insert_contactos($data_array)) {
        echo 'success';
      } else {
        echo 'fail';
      }
    }
  

after running in debug mode, i get this exception from

Exception has occurred.
ErrorException: Uninitialized string offset: 1
"C:\xampp\htdocs\pgp4\system\Database\Query.php"



As always, thank you for your time.

Messed with it again by trying a manually built statement, but still hangs on that exception, the thing is it is inserting the data correctly, but it stops afterwards.
Reply
#2

Just one last shout out to anyone who can give me any insight on this issue.

I have continued to trouble shoot the problem.
I have manually added the insert values directly to model function, so that excludes input type issue.
If I remove all fields but the obligatory one, then it works... I don't understand it.
what is more weird is that the error comes after inserting into database (which it does so correctly by the way).
Reply
#3

(This post was last modified: 05-25-2020, 09:08 AM by kilishan.)

Can you provide a stack trace or the line number in Query that it happens?

On a side note, if you're only doing it for security reasons, I think you gain little benefit over CI's built-in query builder. As long as you don't bypass the features that escape your query you get 99% of the benefit of a prepared statement for a single query.

There are 2 primary advantages of using prepared statements. The first is the security issues, that since they are in the separate calls to the database, there is no chance an unescaped parameter can mess with your query and have unintended side effects. Escaping all query parameters does that also, though it does intermingle them within the same call to the database so there is a very small chance something could slip through. We do use the database engine's own escaping functionality so that should be an extremely rare occurrence.

The second advantage is performance. The statement is prepared by a first call to the database that analyzes the call and builds out the rules it's going to use in the backend. When the prepared query is then ran, all it has to do is insert the variables into the already prepared query. When you're running the same complex query with multiple sets of data, this can be a decent performance increase. If you're only running that query once and then discarding the prepared query, you get zero performance gain, and actually get a bit of a performance loss since that single query is talking to the database engine twice.

In my view, unless the application that you're building is something that needs very high levels of security, like bank/government/medical/personal records type stuff, prepared queries don't offer enough of a security benefit to be worthwhile for security reasons alone. They're a great tool to have in your arsenal if you do find yourself working on those types of sites, or can take advantage of the performance, like with a CSV import/export/whatever.

DISCLAIMER: I am not a security expert so I might have that all wrong, and would be happy to change my opinion if a security expert could educate me. But that's my current opinion based on my understanding of how prepared queries work.
Reply
#4

(05-25-2020, 09:07 AM)kilishan Wrote: Can you provide a stack trace or the line number in Query that it happens?

On a side note, if you're only doing it for security reasons, I think you gain little benefit over CI's built-in query builder. As long as you don't bypass the features that escape your query you get 99% of the benefit of a prepared statement for a single query.

There are 2 primary advantages of using prepared statements. The first is the security issues, that since they are in the separate calls to the database, there is no chance an unescaped parameter can mess with your query and have unintended side effects. Escaping all query parameters does that also, though it does intermingle them within the same call to the database so there is a very small chance something could slip through. We do use the database engine's own escaping functionality so that should be an extremely rare occurrence.

The second advantage is performance. The statement is prepared by a first call to the database that analyzes the call and builds out the rules it's going to use in the backend. When the prepared query is then ran, all it has to do is insert the variables into the already prepared query. When you're running the same complex query with multiple sets of data, this can be a decent performance increase. If you're only running that query once and then discarding the prepared query, you get zero performance gain, and actually get a bit of a performance loss since that single query is talking to the database engine twice.

In my view, unless the application that you're building is something that needs very high levels of security, like bank/government/medical/personal records type stuff, prepared queries don't offer enough of a security benefit to be worthwhile for security reasons alone. They're a great tool to have in your arsenal if you do find yourself working on those types of sites, or can take advantage of the performance, like with a CSV import/export/whatever.

DISCLAIMER: I am not a security expert so I might have that all wrong, and would be happy to change my opinion if a security expert could educate me. But that's my current opinion based on my understanding of how prepared queries work.

Thank you so much for spending the time to help me.
So, I have started manually escaping the input, but now that you shared this with me I am now only escaping the the queries that are not made using query builder.
And when i need to get data from database to show to user i just use htmlspecialchars() to be able to display quotes and tags.

As for the error, here is the line 481   $escapedValue = $binds[$c][1] ? $this->db->escape($binds[$c][0]) : $binds[$c][0];

PHP Code:
protected function matchSimpleBinds(string $sql, array $bindsint $bindCountint $ml): string
    
{
        
// Make sure not to replace a chunk inside a string that happens to match the bind marker
        
if ($c preg_match_all("/'[^']*'/i"$sql$matches))
        {
            
$c preg_match_all('/' preg_quote($this->bindMarker'/') . '/i'str_replace($matches[0], str_replace($this->bindMarkerstr_repeat(' '$ml), $matches[0]), $sql$c), $matchesPREG_OFFSET_CAPTURE);

            
// Bind values' count must match the count of markers in the query
            
if ($bindCount !== $c)
            {
                return 
$sql;
            }
        }
        
// Number of binds must match bindMarkers in the string.
        
else if (($c preg_match_all('/' preg_quote($this->bindMarker'/') . '/i'$sql$matchesPREG_OFFSET_CAPTURE)) !== $bindCount)
        {
            return 
$sql;
        }

        do
        {
            
$c--;
            
$escapedValue $binds[$c][1] ? $this->db->escape($binds[$c][0]) : $binds[$c][0];
            if (
is_array($escapedValue))
            {
                
$escapedValue '(' implode(','$escapedValue) . ')';
            }
            
$sql substr_replace($sql$escapedValue$matches[0][$c][1], $ml);
        }
        while (
$c !== 0);

        return 
$sql;
    } 

at lunch time I will remake everything to reproduce the error and get you a stack trace.

thank you for your answer, I am now applying changes accordingly.
Reply
#5

I have reproduced the error, the thing is because of my lack of knowledge i can not get you the stack trace.
That is because I am using an ajax method to send data to insert and when the error occurs, the page just stays the same without running the success response from the ajax method.
It is only when I run the xdebug during the operation that i can catch it.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB