Simple Invoices - Forum

mvandorp

Hi,

I have a problem with dbQuery, specifically the variable substitution.

As there is little documentation for this (I could not find it, anyway), I try things by using existing code as an example.

In sql_queries.php, dbQuery is often called like this:

$sql="SELECT * FROM table WHERE id = :id"
$sth=dbQuery($sql, ':id', $biller_id);

In this example, the table named 'table' is queried, where the field 'id' contains the value of variable 'biller_id'

The ':id' part of the SQL string should be replaced by the contents of $biller_id (probably done by

$sth = $dbh->prepare($sqlQuery);

in the function 'dbQuery'

In the log table (and in some debug output), the variable substitution is not always performed. The log shows as SQL statement:
SELECT * FROM table WHERE id = :id

instead of (assuming $biller_id = 1) the intended

SELECT * FROM table WHERE id = 1

Question:

What are the 'rules' for using this function?

Marcel

justin

hey marcel

re :id stuff
- we use parameter binding with pdo to make the sql queries safer from sql injection xss hacks
- refer: http://au.php.net/manual/en/pdostatement.bindparam.php

only rules are not to use a php variable directly in the sql - use the :variable_name and then put the bound variable followed by the variable in the dbQuery(.. section - refer below for example

$sql="SELECT * FROM table WHERE id = :id and name = :name and date = :date"
$sth=dbQuery($sql, ':id', $biller_id, ':name', $biller_name, ':date', $invoice_date );

hope this info helps - let me know if you have any queries

Cheers

Justin

mvandorp

I figured that out myself, but it does not always work.

In si_log I then still see the :id placeholder in the query (and the query result returns the wrong result set)

See the bottom of init.php (latest svn)

MArcel

justin

hey marcel

not sure if si_log will be useful anymore due to the parameter binding

all queries show up the the mysql log (assuming logging is on) anyway - so we may have to remove that feature

cheers

justin

mvandorp

From the SQL log (I restarted mysql to enable logging):

170 Query UPDATE si_extensions SET enabled = '0' WHERE id = '12' AND domain_id = '1' LIMIT 1

but to si_log:

169 Query INSERT INTO si_log (timestamp, userid, sqlquerie, last_id) VALUES (CURRENT_TIMESTAMP , '1', 'UPDATE si_extensions SET enabled = :status WHERE id = :id AND domain_id = :domain_id LIMIT 1', '0')
170 Query SELECT * FROM si_extensions WHERE domain_id = 0 OR domain_id = '1' ORDER BY name

I don't know if the restart changed anything, but it appears that dbQuery is now working as expected.

I'll upload the change

Marcel

justin

thanks marcel

Simple Invoices - Forum

Howdy, Stranger!

Categories