I have a desktop app that needs to send data to a MySQL Server. The app will be for internal company use, but the MySQL is on a server at a hosting company.
The data will need to be massaged a bit before being inserted and standard simple insert, delete and update.
Which should I use PHP or Perl?
I use PHP now for a variety of database driven web pages, but my current task has no need to any web rendering. (I know PHP could do this without an web-rendering too)
I have used Perl in the past (maybe 4 years ago) for a data mining task and Oracle.
What I don't know: - Can Perl work with MySQL easily? - The Perl scripts would go in cgi-bin on the webserver, correct? - Security issues with either? - Best practice in Perl for connecting to MySQL to insert data? - Where can I store the Insert, Update, Delete MySQL user name and password so it is not stolen, etc?
What would you all choose?
Thoughts are appreciated
Perl's DBI module is very powerful and makes it easy to avoid SQL injection attacks through the use of bound parameters. See 'perldoc DBI' for details on the use of placeholders in SQL statements. Protecting the username/password for the database can be done with normal Unix file protection by placing the credentials in a read-protected file.
The DBIx::Class and Class::DBI module provides a way to map the entire database and relations into Perl objects. It makes it very easy to rapidly walk the database, generating secure code along the way without needing to write a single line of SQL. This comparisons of Class::DBI and DBIx::Class can help you choose which one; as usual with Perl, there is more than one way to do it. If you're looking for an entire framework, the Rose library provides Rose::DB::Object to streamline access.
You can run the Perl scripts either from the command line or in the cgi-bin directories.
[ Thanks for draegtun for suggestions of other database wrapper classes ]
Well both can work just fine with MySQL. It's really just a preference and level of comfortability. Since you say you do quite a bit with PHP, then that might be more productive for you.
Perl's DBI is very similar to PHP's PDO or object-oriented mysqli, but if you've never used either now might be a good time to learn one...
PHP works fine from the command line. If it is what you are familiar with, and more importantly what the company is familiar with (think of who has to maintain it after you).
Perl, because DBI is much more mature and has more infrastructure around. But if you're sure you will stick to mysql, the "ordinary" binding from PHP is pretty well tested and used by the majority of PHP database programmers.
If you are familiar with writing web apps in PHP, you already know how to write console apps with it.
The only difference is you are outputting plaintext instead of HTML, and some new superglobals $argc and $argv which contain the command line arguments.
If you use PDO or the MySQLi class you can use paramaterized or prepared queries.
Working with the DB probably would be just as easy in either language, so for your app the main thing you will have to decide is which language do you feel more comfortable manipulating data with?
Can Perl work with MySQL easily?
Yes, LAMP used to be Linux, Apache, MySQL and Perl. Amateurs use PHP instead :-P. More seriously PHP has got much better over the last few years, but Perl is more mature so use it if you can do so without compromising delivery deadlines.
The Perl scripts would go in cgi-bin on the webserver, correct?
As long as your web server can access and execute the script, it can go in any directory you like. The existence of a cgi-bin directory is simply an effort to separate static pages from dynamic ones. Simply put, if your webserver can run PHP from a directory, it should be able to run Perl from there too.
Security issues with either?
Try not to write SQL that is vulnerable to SQL Injection attacks; do NOT use the old PHP
mysql_ functions, use
mysqli_ functions instead. With both PHP and PERL, use bound variables and check all input.
Best practice in Perl for connecting to MySQL to insert data?
use the Perl DBI library, it is very mature and if used properly with bound variables, restricts the ability to perform SQL Injection attacks.
Where can I store the Insert, Update, Delete MySQL user name and password so it is not stolen, etc?
Simply put you can't give an absolute guarantee. Create a MySQL user with the minimum rights you need for the program to operate. i.e. the user should not have the rights to remove tables, drop databases etc. You can put your database related code outside the directory that is accessible by the webserver to prevent casual downloading of the critical file.