Notes

This page explains databases one can use with Powtils.

Is there database support?

There are several databases that one can use with Powtils. The most common economical database that is available on many CPanel/Plesk accounts is MySQL. Others prefer Firebird, PostGre, SQLite, etc. There is also a text based database included with the powerful web utils called SDS which is more geared toward small and simple databases.

To use a database with Powtils, download a third party unit (there are many included with freepascal) and put the units into your uses clause of your program. One advantage of powerful web utilities is that you can utilize any external units that aren't even part of Powtils.

Many External Units Available

There are a plethora of units available for database work.

• The FCL-DB package included with FPC. See some FCL-DB notes and docs.

• Third party wrappers such as

  • ZeosLib

  • PDO (pascal data objects)

  • LibSql

  • Sqlite4FPC

  • fbLib for FireBird

  • DirectSQL objects MySQL

  • tiOPF (object persistence framework)

  • There are some more? Authors, send us your links.
• MySQL Direct API for different versions of MySQL:

  • MySQL 3

  • MySQL 4

  • MySQL 4.1

  • MySQL 5.0

  • Install the mysql package during the fpc installation to use the above units in your uses clause. The mysql units are also available in the FPC sources in the /packages/base/mysql/ directory.
• Are more direct API units available? Yes, third party wrappers such as LibSQL, ZeosLib, and PDO also contain direct API units similar (near duplicate) to the above MySql ones. All the third party wrappers have to talk to the databases through direct database API calls at some point.

• There are more, above is just a sample of popular database units.

Other Notes

If you are having trouble getting started with a database like MySQL 4 or similar, see this example.

If you are using the freepascal compiler, your installation included many database units. There are database wrappers or direct api database units for several common databases included with FPC. If you do not have database units installed, reinstall freepascal and choose the packages which you want to try.

It can be overwhelming to try all the third party database units out which wrap several databases into one framework. If you are unsure of which one to use, or you find that you are overwhelmed with too many database access choices, then you are probably best just picking one and seeing if you like it.

SQL is fairly portable, so even if you use the direct API calls to a specific database, most of your database queries will be fairly portable - as long as you don't use obscure database specific queries.

SQL Injection

There are some tips about SQL injection here. Use prepared statements/parameterized queries if possible.

What about SqlEscape()?

You can use embedded SQL (the old PHP way) in your programs but it is much less secure. It is also known as dynamic SQL. It is not recommended if you have alternatives. Most people are used to the PHP way.

Escaping SQL is not bullet proof. Some SQL injections still slip through. Escaping also bloats the source code with Escape() calls. If possible, use parameterized/prepared queries. If your database does not support that, then using SQL templates that have $macrovars in them which are carefully escaped by you before generating the SQL is recommended. That would essentially be emulating database server prepared SQL, but instead you do the preparing yourself with the web program.

If you are just writing a throwaway program, using embedded SQL in your programs is not such a big issue - but some throwaway scripts end up becoming real world programs later.. so be careful.

lufdoc, Powtils, fpc, freepascal, delphi, kylix, c/c++, mysql, cgi web framework docs, Z505