MySQL + General Security

[eluser]insub2[/eluser]
I made a pretty simple app for a client a bit ago and we are now beginning to make some adjustments. I guess he has a friend who specializes in internet security that has managed to harvest data directly from the database. I'm wondering how this is done? And more importantly, how to prevent it?

Unfortunately, the current host doesn't allow moving anything outside of the web root. To my understanding, this is a security risk though I don't quite understand why. Shouldn't all the PHP be parsed before anything leaves the server?

The other thing I'm suspicious of is the MySQL server is not the same as the web server. I did't use any sort of encryption because, well, I wasn't really aware it was a big deal when I set up the site. Though, we are going to be switching hosts soon, and I was wondering if encryption is necessary on local MySQL server?

[eluser]Jelmer[/eluser]
The problem with the PHP files being within your webroot is that someone could access individual PHP files and try to bypass any security measures in a controller by accessing a library or model directly. CI comes with index.htm files to prevent directory listing (which can be used to find out the names of your files) and all the files start with checking if the BASEPATH constant is set as a rudementairy check on wheter it's loaded trough CI.

But as for someone accessing your SQL. The most likely trick is "SQL Injection". Other regular security riscs are "Cross-Site Scripting" (XSS or CSS) and "[url =http://en.wikipedia.org/wiki/Cross-site_request_forgery]Cross-Site Request Forgery[/url]" (CSRF or XSRF). I've linked the words to their Wikipedia pages where you can read up on how they work.

For solutions to such problems try searching the Wiki, the forums and Google - there's a lot of information and solutions out there.

[EDIT:] I should also mention the page in the User Guide on Security.

[eluser]insub2[/eluser]
Thank you for your reply. I'll be sure to look at those links. But I'm curious, how does one look at php source code? I was told over and over that the php parser is run on any php file before it outputs anything?

[eluser]Jelmer[/eluser]
As far as I know you can't look at source code directly, but there are tricks to discover stuff about the source code. One of the most often made mistakes is to have display_errors on in a public environment. As helpful as those errors are during development, they can tell people about what happens where in your file. Same goes, but even more dangerous, for displaying SQL errors (= people can actually debug their hack attacks with help from your own server).

Or maybe another file on your server is used to read and display the file, if you have such a securtiy hole all your data is at risk of course because it isn't too hard to find the config files after that.

[eluser]Skinnpenal[/eluser]
It could also be that if you're on a shared host, the guy has access to an account at that host too. Then he could easily with for example a php script browse his way onto your account and read the files, where he'd find the db config among others.

Many shared hosts (often a user mistake too I guess) has some access issues that allows for this to happen.

[eluser]Jon W[/eluser]
Good replies above there I think. What evidence do you have that they actually breached the db server? Could that information have been gathered in other ways?

Anyways, stuff to check would be;
- don't write your own SQL, use the CI db class (helps your sanity and preventing SQL injection).
- "Shouldn’t all the PHP be parsed before anything leaves the server?" Jelmer covered that, one addition is stopping parsing of PHP by the web server is possible but would require modifications to the web server's configuration itself, and there's probably easier ways to get at your source than getting control of the httpd, so that seems unlikely.

My bets would be
1) it's the shared hosting, so many of them are wide open once you have an account on there.
2) db credentials were stored in the PHP source instead of elsewhere, and someone browsed your source.
3) use of FTP credentials which were sniffed.
4) you got XSS'ed or XSRF'ed and they stole an admin session off you for your app, or PHP db management interface or similar.

One thing to ensure is to be a tight as possible with the db authentication credentials, i.e. only allow access from certain IP's with long, strong passwords, ensure you have changed the admin password after install etc. as then at least they have to be able to modify files on your web app server to get at the data (yep IP's can be spoofed but that's non-trivial). Otherwise they can sit there and try to brute-force the mysqld.

Encrypting communications to a remote db server would prevent wire-sniffing type attacks (assuming they know where your db server is), but that's quite a processor overhead & there's easier ways to get at that data by the sound of it. When the db is on the same local box the connection is via unix local sockets not over the wire, so they can't port-sniff you (if the db is properly locked down to only accept connections via unix local sockets or 127.0.0.1 or something). If it is on shared hosting still, it is possible for another user to snoop on that unix local socket connection but it's not trivial IIRC. If you are on shared hosting you have bigger security issues, so I wouldn't bother encrypting the connection to a local mysqld.

Edit: there are several gotchas to running mysqld's on a local machine, mainly don't enter passwords from the shell (they may show up in the process list) and turn off networking in the my.cnf. I suggest you read up on securing mysqld's before setting it up

[eluser]johanriyan[/eluser]
Hello Guys,

I have error like this :

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-2, 2’ at line 4

SELECT * FROM (`user`) ORDER BY `no` ASC LIMIT -2, 2

Filename: D:\xampp\htdocs\cibootstrap\system\database\DB_driver.php

Line Number: 330


this error appear when i am add ” - ” in the pagination.


this is my model :

Code:

<?php

class User_model extends CI_Model{

    function get_all($num, $offset)
    {
        
        $this->db->order_by('no', 'ASC');
        $data=$this->db->get('user', $num, $offset);
        return $data->result();
    }
    
    function save($data){
        
        $this->db->insert('user',$data);
    }
}

and this is my controller :

Code:

public function about($id=NULL){

//pengaturan pagination
$jml = $this->db->get('user');

$config['base_url'] = base_url().'index.php/bootstrap/about';
$config['total_rows'] = $jml->num_rows();
$config['per_page'] = '2';
$config['first_page'] = 'Awal';
$config['last_page'] = 'Akhir';
$config['next_page'] = '«';
$config['prev_page'] = '»';





//inisialisasi config
$this->pagination->initialize($config);
      

       //buat pagination
$data['halaman'] = $this->pagination->create_links();
      
    

       //tamplikan data
    $data['query'] = $this->user_model->get_all($config['per_page'], $id);
$this->load->view('include/header');
$this->load->view('about',$data);
$this->load->view('include/footer');


}

how to fix it.