PHP codeigniter SQL Injection(MSSQL)

Question

I was receiving the the report show my query has SQL injection, would need your help to verify some problem:

I was using this as the insert statement

  function insert($table_name, $insert_data)
    {
        $this->db->set($insert_data);
        if ($this->db->insert($table_name)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

for exmaple:

$name = 'iamklll';
$status = 1;

  $insert_data =
                array(             
                'name' => $name,
                'status' => $status,
                );
  $this->model->insert('table', $insert_data);

This kind of method cannot prevent the SQL injection? If this cannot prevent I think best way is query bindings?

How do I prevent the SQL injection if the $POST['column'] is array?
How to store the data as if the person insert test"><h1>eee</h1>? Should I use htmlspecialchars to store in database?

What version of Codeigniter are you using? – Yevgen Oct 27 '20 at 10:47 — Yevgen, Oct 27 '20 at 10:47

score 0 · Answer 1 · answered Oct 27 '20 at 11:05

0

You are referring to Active Record class. It has own query builder and it suppose to protect you from SQL injections from the box.

How to store the data as if the person insert test"><h1>eee</h1>

I think you are referring to XSS attack. I believe that the best practice it to strip out all the tags except allowable tags. I don't think htmlspecialchars will make much sense here while you are going to make backward converting on the output.

answered Oct 27 '20 at 11:05

Yevgen

1,239
3
15
30

if my database store like ```test">
eee
``` is it ok? – phpguy Oct 27 '20 at 11:09
It depends who puts this data and who gets it. If the regular user can break down the markup it is not good, but i don't see anything wrong with HTML tags inside database in general. You might also consider reading about `global_xss_filtering` config option in CI. – Yevgen Oct 27 '20 at 11:13

score 0 · Accepted Answer · answered Oct 27 '20 at 11:13

In your case you're using the query builder $this->db->insert() this generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. All values are escaped automatically producing safer queries.

This is actually the best option in my opinion. Better than using the query bindings, specially because in the long run you might need to change from MSSQL to mySQL or any other engine and you wont need to change anything in your code.

Since you're using codeigniter 3 be sure to validate your data before inserting it.

Use the form validation for that: https://codeigniter.com/userguide3/libraries/form_validation.html?highlight=form%20validation

From your example I think you're also referring to xxs attacks. If so, you have two options, you can do this while validating your data with form validation using the XSS rule. Or you can activate this globally in your application/config/config.php

$config['global_xss_filtering'] = true;

Since we're talking about protecting your app you might also want to consider using the csrf settings in codeigniter. This can also be done in your config.php file.

/*
|--------------------------------------------------------------------------
| Cross Site Request Forgery
|--------------------------------------------------------------------------
| Enables a CSRF cookie token to be set. When set to TRUE, token will be
| checked on a submitted form. If you are accepting user data, it is strongly
| recommended CSRF protection be enabled.
|
| 'csrf_token_name' = The token name
| 'csrf_cookie_name' = The cookie name
| 'csrf_expire' = The number in seconds the token should expire.
| 'csrf_regenerate' = Regenerate token on every submission
| 'csrf_exclude_uris' = Array of URIs which ignore CSRF checks
 */
$config['csrf_protection'] = true;
$config['csrf_token_name'] = 'csrf_test_name';
$config['csrf_cookie_name'] = 'csrf_cookie_name';
$config['csrf_expire'] = 7200;
$config['csrf_regenerate'] = true;
$config['csrf_exclude_uris'] = array();

So, in your case, using the query builder is fine to prevent SQL injection of that's what you're actually looking for.

PHP codeigniter SQL Injection(MSSQL)

2 Answers2

eee