[svlug] PHP database code not...

Ivan Sergio Borgonovo mail at webthatworks.it
Fri Nov 14 03:05:54 PST 2014


On 11/14/2014 05:04 AM, Michael Robinson wrote:

>>>           $result = pg_query($db,$query);

>> pg_fetch_row — Get a row as an enumerated array.
>> pg_fetch_row() fetches *one* row of data from the result associated with
>> the specified result resource.
>>
>>>           $compid = pg_fetch_row($result);
>>
>> It looks like this line have to be *in* the loop.

/\*in\* the/in another/

>>>           foreach ( $compid as $element )
>>>           { echo "<P>$element</P>"; }

execute query
loop through rows
  loop through columns

> If this were accessed in any way other than over a local area network,
> input validation would be mandatory and I'm not certain how I would
> verify that I'm connecting to the right database.

That's not the main issue and I'm tempted to bet that if you looked at
php documentation at least you would have seen an example that would
avoid the most simple SQL injection.

You DON'T concatenate strings manually to obtain SQL statements.

This is one of the way:
http://php.net/manual/en/function.pg-query-params.php

Generally you use a higher level of abstraction like PDO eg.:
http://php.net/manual/en/pdostatement.execute.php

If you want to check you're connecting to the right DB you've to use
certificates and an ssl connection.
http://www.postgresql.org/docs/9.1/static/ssl-tcp.html

> values I'm getting for computer_id are errors?  The code should include
> input validation and correct database validation, but right now I'm just
> trying to keep things simple.  How do I get the correct computer_id for
> each computer I want to associate with a user_id in another table?

It's a good idea when you're trying to solve DB problem to show your DB
schema aka the CREATE TABLE statements you have or would like to have.

I suppose you already have a table with uid somewhere and you're trying
to solve a larger problem and not eg. this simpler one:

create table computerpeople (
  uid integer,
  uname text,
  cid integer,
  cname text
)

but rather something like:

create table people (
  uid integer primary key,
  name text not null,
 );

create table computer (
  cid integer primary key,
  name text not null,
  uid references people (uid) set null
);

select c.cid from people as p
 join computer as c on p.uid=c.uid
  where p.name='SOMENAME'

and variations.
My crystal ball is on repair.

Look for some SQL tutorial on the net.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it





More information about the svlug mailing list