0

I've been developing a CRM system and the plan was to set it up on individual hosting for each individual client. The system is already about 95% complete and now I'd like to instead have the CRM in one place and be able to have each client access it and have their version of the CRM shown to them based on their login.

I've been developing this for over 2 years so there is a lot of code. In order to have one CRM with many clients I will need to change the databases to function based on a client ID.

That should mean:

  1. Updating the SQL tables
  2. Updating almost every bit of code in the PHP which connects to the mySQL database to include the client ID so it fetches the relevant data for that client.

In regards to number 1, that would be simple and quick to do.

However in regards to number 2, since the system is almost complete, there are probably over a thousand different places in the code which will need to be modified to include the client ID in the SQL statement to fetch/update/insert the relevant data for that client.

An example of some PHP in my code which connects to the mySQL database:

try {
    $sql = "INSERT INTO call_list_recommend SET candid='".$candid."',".
                                                "listid='".$recommended_list."',".
                                                "rowid='".$rowid."',".
                                                "transfer_type='".$transfer_type."',".
                                                "byuser='".$_SESSION['username']."',".
                                                "date=date_format(curdate(), '%d/%m/%Y')";
    $result = $pdo->query($sql);
}
catch(PDOException $e) {
    $error = 'Error recommending candidate for call list: ' . $e->getMessage();
    showerror($error);
    exit();
}

I would have to add the client ID to that SQL, and there would be over a thousand different places in code in various files which would also need to be updated.

Would this be the only way to achieve this or am I overlooking another perhaps quicker and more efficient way of achieving this goal?

dlofrodloh
  • 1,728
  • 3
  • 23
  • 44
  • 2
    But why? Keeping it separate can be a lot better. What You are trying to do right now is redesigning the whole solution which would take a lot of time. and all of us know that time=money. Either go with the new project and apply some code parts from the previous one or keep it as is. It should not generate You a lot of costs since You can keep it on one hosting. Another approach would be to create a top service/page with login page and based on user redirect with a session to the right page. From my exp such big changes end up with a lot of bugs in the future.. always! – malutki5200 Dec 12 '16 at 15:15
  • 1
    this code is vulnerable to SQL injection. This is a new application by the sounds of it, and one to be used by paying customers. Why aren't you using parameterised queries? If the code was 10 years old there might be some leeway, but for a brand new app there is really no reason not to do things correctly and securely. – ADyson Dec 12 '16 at 15:17
  • @malutki5200 I like the idea of a top service login page with the redirects, thanks. That could be the solution, I'll give that some more thought. – dlofrodloh Dec 12 '16 at 15:27
  • @ADyson All PHP variables used in the SQL statements go through htmlspecialchars() before I use them to protect against SQL injections. – dlofrodloh Dec 12 '16 at 15:28
  • 1
    Are you expecting a lot of different users? I.e. would it be feasible to create a new database each time and then simply name that user in your db connector? (i.e. connect to database_userID). – Luke Briggs Dec 12 '16 at 15:38
  • Also wrt injection, `$_SESSION["username"]` isn't being escaped. I would imagine your system accepts quotes in the username - at which point, it's just a matter of time before it gets passed into that function. – Luke Briggs Dec 12 '16 at 15:41
  • 1
    @dlofrodloh the intention of that function is to guard against XSS injection and similar web-related problems. It's not designed for anything to do with SQL (the clue's in the name!). Although people do use it, there's plenty of material and examples of where it wouldn't help: https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#safe=active&q=htmlspecialchars+sql+injection . Here's the canonical SO answer on how to prevent SQL injection in PHP: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 – ADyson Dec 12 '16 at 15:42
  • @LukeBriggs, that's also a very good point. The database it connects to is from the PDO object which could point to a different database depending on the client. In regards to the $_SESSION["username"] in the SQL statement, the username goes through htmlspecialchars when you create the username and doesn't allow certain characters. – dlofrodloh Dec 12 '16 at 15:45
  • 1
    **WARNING**: When using PDO you should be using [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) with placeholder values and supply any user data as separate arguments. In this code you have potentially severe [SQL injection bugs](http://bobby-tables.com/). Never use string interpolation or concatenation and instead use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) and never put `$_POST` or `$_GET` data directly in your query. Refer to [PHP The Right Way](http://www.phptherightway.com/) for guidance with this and other problems. – tadman Dec 12 '16 at 15:46
  • 2
    `htmlspecialchars` has **nothing** to do with SQL injection protection and in fact is utterly useless. This is why you must use prepared statements with placeholder values. It makes it obvious you're doing it correctly. – tadman Dec 12 '16 at 15:47
  • @ADyson, I'll have to look more into this. I was under the impression htmlspecialchars completely protects against SQL injection when you also include the ENT_QUOTES flag. Perhaps I'll have to look more into this. – dlofrodloh Dec 12 '16 at 15:49
  • 1
    @dlofrodloh nope - example: https://eval.in/695718 – Luke Briggs Dec 12 '16 at 15:52
  • @LukeBriggs echo "name='".htmlspecialchars($evilString, ENT_QUOTES, 'UTF-8')."'"; seems to be fine – dlofrodloh Dec 12 '16 at 16:18

0 Answers0