Refactor our CRM

We are looking for a few good PHP coders, who are well versed in best practices to help us refactor our CRM. The CRM is presently in-flight and deployed to several facilities, but as a product is has grown organically over the years. This is the first phase of many and anyone brought in at this phase will be on our shortlist for the future phases. Phase 1 consists of analyzing and refactoring any code that may be insecure. Automated tools have identified roughly 10,000 lines of code that suffer from various issues such as injection vulnerabilites, leaky constructs, failure to release resources, susceptibility to buffer overflows etc. The total impact of this refactor is expected to touch upon approximately 25,000 lines of code. Your expected workload is an average of 1,000 lines per day. This will be verified by our git repository. The length of this phase depends on how many applications we bring on, however the delivery date on this phase is December 1st, 2014. The existing product is written in PHP5 and uses mysql as a back-end. Therefore we are looking for people with moderate skills in PHP5 & SQL. During Phase 3 we will be moving off from MySQL and onto something else, which will likely be PostgreSQL. In order to make that move as easy as possible, this phase of the project will remove all calls to mysql & replace them with PDO equivalent constructs. For the most part this will be boiler plate and repetitive. You will be part of a team that may comprise as many as 10 other developers. You will need to be able to focus and pay close attention to detail and communicate effectively. There is an IRC channel you can log into with any questions and for general concerns. You can pick your own hours so long as you maintain at least the minimum 1,000 lines per 8hr shift. The best performers will be invited to Phase 2 which launches the first week of December and includes much more interesting work. So consider this our applicant screening process. The product originated in latin america, the source code is in Spanish. You should be able to communicate effectively in both languages, but at a minimum you should be able to speak & write in english fluently, while being able to read spanish at least at an intermediate level. If you apply to this position in broken english you will be ignored, unless you are from a spanish speaking country. As part of the application process we ask each person to evaluate and refactor a small section of code. Given the following code... function firmaIDByUsuario($usuario) { $conexion = abrirconexion(); $consulta = "SELECT usr_id_usuario FROM usuarios WHERE usr_usuario = '$usuario' AND usr_status = 0" ; $resultado = mysql_query($consulta) or die ('No se pudo ejecutar la consulta'.$consulta); if (mysql_num_rows($resultado)>0) { $registro = mysql_fetch_assoc($resultado); return $registro['usr_id_usuario']; } mysql_free_result($resultado); } Please explain to me the various mistakes that the original author made. Assuming you were a part of the team that had to refactor this section of code, how would you rewrite it so that no longer suffers from any of the same problems that the existing code does while not introducing any new issues? Things to consider... Is this code following best practices already? Why or why not? Is this code exploitable? Why or why not? If it were exploitable, what were the nature of the exploits you've uncoveed? Does this code leak resources? Why or why not? Assuming that abrirconexion were modified to return a properly opened connection to the database via a PDO connection object, and a cerrarconexion function were created that would close the resulting connection object when called, how would you modify this code to use PDO prepared statements instead of mysql_query? Please submit your reply privately. Anyone not submitting a detailed explanation via document submission will be ignored.