Thread: New-B Question #2
Folks,
Thanks so much for all your help. Many people quickly replied to my
question with the information I needed.
What I would like to do, use PostgreSQL and PHP, is list all the columns
for a given table. (Having finally choked the list of tables from
postgre). In MySQL it is something like this "SHOW COLUMNS FROM
{tablename} FROM {databasename}". I'm assuming I'm missing something
obvious and bonehead, but I can't seem to translate that into postgreSQL
to save my bacon.
Help?
Thanks,
Char-Lez Braden
On Mon, 2005-08-22 at 16:46 -0400, cbraden wrote:
> Folks,
>
> Thanks so much for all your help. Many people quickly replied to my
> question with the information I needed.
>
> What I would like to do, use PostgreSQL and PHP, is list all the columns
> for a given table. (Having finally choked the list of tables from
> postgre). In MySQL it is something like this "SHOW COLUMNS FROM
> {tablename} FROM {databasename}". I'm assuming I'm missing something
> obvious and bonehead, but I can't seem to translate that into postgreSQL
> to save my bacon.
The command in psql is
\d tablename
or
\d schemaname.tablename
If you start psql with the -E option, it will show you the SQL commands
it runs to satisfy these queries; you can adapt those for use from PHP.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
> What I would like to do, use PostgreSQL and PHP, is list all the columns for
> a given table. (Having finally choked the list of tables from postgre). In
> MySQL it is something like this "SHOW COLUMNS FROM {tablename} FROM
> {databasename}". I'm assuming I'm missing something obvious and bonehead,
> but I can't seem to translate that into postgreSQL to save my bacon.
See the example on this page:
http://us2.php.net/manual/en/function.pg-meta-data.php
-philip
Folks,
I have a simple table called "adjusters" with only these three columns:
id
adjuster
MonthlyGoal
==========COPIED FROM PGAdminIII=============
CREATE TABLE public.adjusters
(
id int4 NOT NULL DEFAULT nextval('adjusters_id_key'::text),
"Adjuster" varchar(50),
"MonthlyGoal" varchar(50),
CONSTRAINT adjusters_pkey PRIMARY KEY (id)
) WITH OIDS;
==============================================
The SQL "SELECT * FROM adjusters" works perfectly
The SQL "SELECT * FROM adjusters ORDER BY Adjuster ASC" results in this
message:
ERROR: Attribute "adjuster" not found
That SQL seems OK to me, but I use mySQL much more than Postgre, so what
do I know?
Anyway, if you see whatever-it-is which is holding me up, please let me
know.
Thanks,
Char-Lez Braden
cbraden <cbraden@douglasknight.com> schrieb:
> Folks,
>
> I have a simple table called "adjusters" with only these three columns:
>
> id
> adjuster
> MonthlyGoal
>
> ==========COPIED FROM PGAdminIII=============
> CREATE TABLE public.adjusters
> (
> id int4 NOT NULL DEFAULT nextval('adjusters_id_key'::text),
> "Adjuster" varchar(50),
> "MonthlyGoal" varchar(50),
> CONSTRAINT adjusters_pkey PRIMARY KEY (id)
> ) WITH OIDS;
> ==============================================
>
> The SQL "SELECT * FROM adjusters" works perfectly
>
> The SQL "SELECT * FROM adjusters ORDER BY Adjuster ASC" results in this
> message:
> ERROR: Attribute "adjuster" not found
Because 'adjuster' is not the same as 'Adjuster'. But, no problem:
SELECT * FROM adjusters ORDER BY "Adjuster" ASC;
Column names are case-sensitive.
Btw.: SELECT * is evil...
Regards, Andreas.
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
On Wed, 24 Aug 2005, cbraden wrote:
> Folks,
>
> I have a simple table called "adjusters" with only these three columns:
>
> id
> adjuster
> MonthlyGoal
>
> ==========COPIED FROM PGAdminIII=============
> CREATE TABLE public.adjusters
> (
> id int4 NOT NULL DEFAULT nextval('adjusters_id_key'::text),
> "Adjuster" varchar(50),
> "MonthlyGoal" varchar(50),
> CONSTRAINT adjusters_pkey PRIMARY KEY (id)
> ) WITH OIDS;
> ==============================================
>
> The SQL "SELECT * FROM adjusters" works perfectly
>
> The SQL "SELECT * FROM adjusters ORDER BY Adjuster ASC" results in this
> message:
> ERROR: Attribute "adjuster" not found
The column is not Adjuster, but "Adjuster". Unquoted identifiers are case
folded in SQL (although we case fold the wrong direction for spec, it
doesn't matter in this case).
> Column names are case-sensitive. > Btw.: SELECT * is evil... > > > > Regards, Andreas. Andreas, what would you recommend instead of SELECT * if you really do wanty all the results? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, Aug 25, 2005 at 16:47:54 -0700, operationsengineer1@yahoo.com wrote: > > Column names are case-sensitive. > > Btw.: SELECT * is evil... > > > > > > > > Regards, Andreas. > > Andreas, what would you recommend instead of SELECT * > if you really do wanty all the results? Explicitly listing the columns. Using '*' causes maintainance problems.
What kind of maintenance problems, if you don't mind my asking? Charley On Aug 25, 2005, at 9:21 PM, Bruno Wolff III wrote: > On Thu, Aug 25, 2005 at 16:47:54 -0700, > operationsengineer1@yahoo.com wrote: > >>> Column names are case-sensitive. >>> Btw.: SELECT * is evil... >>> >>> >>> >>> Regards, Andreas. >>> >> >> Andreas, what would you recommend instead of SELECT * >> if you really do wanty all the results? >> > > Explicitly listing the columns. Using '*' causes maintainance > problems. > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
Please don't top post, it makes it harder to preserve context when replying to your comments. On Fri, Aug 26, 2005 at 09:51:49 -0500, "Charley L. Tiggs" <ctiggs@xpressdocs.com> wrote: > What kind of maintenance problems, if you don't mind my asking? Because if you change your table definition, applications that use '*' in queries will get back different data than they did previously. If you use explicit column lists, only if the listed columns are affected by a change will applications get back different data. '*' is OK to use to save some typing when entering queries by hand, but you shouldn't be using in scripts or applications except in very rare cases. > > Charley > > On Aug 25, 2005, at 9:21 PM, Bruno Wolff III wrote: > > >On Thu, Aug 25, 2005 at 16:47:54 -0700, > > operationsengineer1@yahoo.com wrote: > > > >>>Column names are case-sensitive. > >>>Btw.: SELECT * is evil... > >>> > >>> > >>> > >>>Regards, Andreas. > >>> > >> > >>Andreas, what would you recommend instead of SELECT * > >>if you really do wanty all the results? > >> > > > >Explicitly listing the columns. Using '*' causes maintainance > >problems. While the context should make this obvious, the first period about shouldn't have been there. > > > >---------------------------(end of > >broadcast)--------------------------- > >TIP 6: explain analyze is your friend > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
--- Bruno Wolff III <bruno@wolff.to> wrote: > On Thu, Aug 25, 2005 at 16:47:54 -0700, > operationsengineer1@yahoo.com wrote: > > > Column names are case-sensitive. > > > Btw.: SELECT * is evil... > > > > > > > > > > > > Regards, Andreas. > > > > Andreas, what would you recommend instead of > SELECT * > > if you really do wanty all the results? > > Explicitly listing the columns. Using '*' causes > maintainance problems. thanks for the insight. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Folks, I would like to know how to prevent SQL attacks on a postgreSQL server. I know in mySQL you can put any input going to the DB through a filter which encodes anything which would be malicious into mySQL safe data. I need something similar in postgreSQL. Specifically as a php implementation if it exists. Thanks, Char-Lez
On Fri, Aug 26, 2005 at 15:53:14 -0400, cbraden <cbraden@douglasknight.com> wrote: > Folks, > > I would like to know how to prevent SQL attacks on a postgreSQL server. > > I know in mySQL you can put any input going to the DB through a filter > which encodes anything which would be malicious into mySQL safe data. I > need something similar in postgreSQL. Specifically as a php > implementation if it exists. IMO the best way to do this is to use bind parameters to pass user input to queries. Then you don't need to escape anything. You might still check for very long strings.
> cbraden <cbraden@douglasknight.com> wrote: >> Folks, >> >> I would like to know how to prevent SQL attacks on a postgreSQL server. >> >> I know in mySQL you can put any input going to the DB through a filter >> which encodes anything which would be malicious into mySQL safe data. I >> need something similar in postgreSQL. Specifically as a php >> implementation if it exists. http://us2.php.net/manual/en/function.pg-escape-string.php -philip
On Aug 26, 2005, at 2:53 PM, cbraden wrote: > Folks, > > I would like to know how to prevent SQL attacks on a postgreSQL > server. > > I know in mySQL you can put any input going to the DB through a > filter which encodes anything which would be malicious into mySQL > safe data. I need something similar in postgreSQL. Specifically > as a php implementation if it exists. You can use pg_escape_string () http://www.php.net/pg_escape_string Charley
hi all... how valuable is the PREPARE statement? when should it be used and when should it be avoided? from what i gather, it helps speed up queries (all queries or just some types of queries, i do not know), but it isn't necessarily portable to other dbs. tia... ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
> IMO the best way to do this is to use bind
> parameters to pass user input
> to queries. Then you don't need to escape anything.
> You might still check
> for very long strings.
this got me thinking... is this what you are talking
about (i use ADOdb)?
$db->Execute("INSERT INTO t_customer (customer_name,
customer_entry_date) VALUES (?,?)",
array($customer_name, $db->DBDate(time())));
$customer_name is the validated input from the user
with no escaping of any kind. is this ok?
this query works just dandy. does it mean i can start
sleeping at night? -lol-
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
Please keep replies copied to the list in order to give more people a chance to help and to learn from the discussion. On Fri, Aug 26, 2005 at 16:48:08 -0400, cbraden <cbraden@douglasknight.com> wrote: > Bruno Wolff III wrote: > > >On Fri, Aug 26, 2005 at 15:53:14 -0400, > > cbraden <cbraden@douglasknight.com> wrote: > > > > > >>Folks, > >> > >>I would like to know how to prevent SQL attacks on a postgreSQL server. > >> > >>I know in mySQL you can put any input going to the DB through a filter > >>which encodes anything which would be malicious into mySQL safe data. I > >>need something similar in postgreSQL. Specifically as a php > >>implementation if it exists. > >> > >> > > > >IMO the best way to do this is to use bind parameters to pass user input > >to queries. Then you don't need to escape anything. You might still check > >for very long strings. > > > > > > > > > Sir, > > Being a novice, I did not understand what you meant. Do you know a > reference I can look at to see what you mean? How you do this depends on how you pass SQL queries to the database. For example you might wan to read the Perl DBI module documention or the libpq documention in you are using that from C. Generally there is a different library for each programming language.
On Fri, Aug 26, 2005 at 15:40:02 -0700,
operationsengineer1@yahoo.com wrote:
> > IMO the best way to do this is to use bind
> > parameters to pass user input
> > to queries. Then you don't need to escape anything.
> > You might still check
> > for very long strings.
>
> this got me thinking... is this what you are talking
> about (i use ADOdb)?
>
> $db->Execute("INSERT INTO t_customer (customer_name,
> customer_entry_date) VALUES (?,?)",
> array($customer_name, $db->DBDate(time())));
>
> $customer_name is the validated input from the user
> with no escaping of any kind. is this ok?
>
> this query works just dandy. does it mean i can start
> sleeping at night? -lol-
Yes this is the idea. Bad data for the values can't execute unexpected SQL
commands; it can only cause the query to fail.
--- Bruno Wolff III <bruno@wolff.to> wrote:
> On Fri, Aug 26, 2005 at 15:40:02 -0700,
> operationsengineer1@yahoo.com wrote:
> > > IMO the best way to do this is to use bind
> > > parameters to pass user input
> > > to queries. Then you don't need to escape
> anything.
> > > You might still check
> > > for very long strings.
> >
> > this got me thinking... is this what you are
> talking
> > about (i use ADOdb)?
> >
> > $db->Execute("INSERT INTO t_customer
> (customer_name,
> > customer_entry_date) VALUES (?,?)",
> > array($customer_name, $db->DBDate(time())));
> >
> > $customer_name is the validated input from the
> user
> > with no escaping of any kind. is this ok?
> >
> > this query works just dandy. does it mean i can
> start
> > sleeping at night? -lol-
>
> Yes this is the idea. Bad data for the values can't
> execute unexpected SQL
> commands; it can only cause the query to fail.
nice! pretty painless, too. do you mind spending a
minute explaining the mechanics? obviously, the
format does a little more than just input the values,
otherwise it would be just like the other format.
if bad data is submitted, is there something going on
"behind the scenes" to scrub the bad data and cause
the query to fail instead of run with the bad data?
how does the the system know the data is bad data?
this is new to me, but very interesting.
tia...
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On Sat, Aug 27, 2005 at 16:22:51 -0700, operationsengineer1@yahoo.com wrote: > > if bad data is submitted, is there something going on > "behind the scenes" to scrub the bad data and cause > the query to fail instead of run with the bad data? > how does the the system know the data is bad data? The type input routines will reject bad data. I wouldn't feel too safe about handling really large strings without a problem in all cases, but invalid syntax shouldn't cause anything but the transaction to abort.
--- Bruno Wolff III <bruno@wolff.to> wrote: > On Sat, Aug 27, 2005 at 16:22:51 -0700, > operationsengineer1@yahoo.com wrote: > > > > if bad data is submitted, is there something going > on > > "behind the scenes" to scrub the bad data and > cause > > the query to fail instead of run with the bad > data? > > how does the the system know the data is bad data? > > The type input routines will reject bad data. I > wouldn't feel too safe about > handling really large strings without a problem in > all cases, but invalid > syntax shouldn't cause anything but the transaction > to abort. hi Bruno, Can you give us an idea of what a "really long string is?" is it something i need to worry about in varchar field where notes are entered? if someone enters a string note like: "test unit failed; os2; likely failure; where t=2" will it fail b/c of the ";"s? sorry for so many questions, but i didn't know anythign about this until it came up in the mailing list. tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Sat, Aug 27, 2005 at 18:55:54 -0700, operationsengineer1@yahoo.com wrote: > --- Bruno Wolff III <bruno@wolff.to> wrote: > > > Can you give us an idea of what a "really long string > is?" is it something i need to worry about in varchar > field where notes are entered? > > if someone enters a string note like: > > "test unit failed; os2; likely failure; where t=2" > > will it fail b/c of the ";"s? That wouldn't be a problem. If someone entered one that was gigabytes in size it may be a problem in terms of flushing your memory of useful things and slow performance down. If there is a bug, a few kilobytes could cause a problem. If you know the data can't be more than a few 10s of bytes you probably want to not process ones much larger as it is probably someone trying to mess with you.