[SQL] Get x from point?
Hi all, I am wondering if we are able to extract the x-coordinate from a point-type attribute with SQL. I have been looking for this sort of functions from documents and websites but it seems the function does not exist. Or, any altnernate to do so? regards, Daniel Lau Hong Kong University of Science and Technology ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to preserve \n in select statement
On Saturday 20 December 2003 17:58, Matt Van Mater wrote: > > I readily admit that I didn't read much into it, but I think you are > mistaken. If you look at the following links you will see that postgresql > definitely had a vulnerability that allowed sql injection through the use > of escape characters. I was simply saying that this behavior might be a > way of preventing that from happening again. > http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802 > http://cert.uni-stuttgart.de/doc/postgresql/escape/ Ah - this "vulnerability" is still there - and, in fact is in every database ever produced. The issue is that applications using untrusted data to build a query string can be duped by a malicious user. So - say you have a query template: SELECT * FROM recent_news WHERE topic='?' If a malicious user supplies the string: '; DELETE FROM user_logins; SELECT ' then you will have the resulting query string: SELECT * FROM recent_news WHERE topic=''; DELETE FROM user_logins; SELECT '' If you allow multiple queries in one string, there is no way to prevent this. If you disallow multiple queries, there are still attacks that are possible. This is why it is vital to parse and validate user input. If you are asking for an integer, check that it is. If you are asking for a string, quotes etc. need to be escaped (AFAICT the stuttgart url describes a function that lets you do this - you still need to call it). The first URL seems to deal with an old bug that meant a cunning attacker could bypass your escaping. It is still vital that the application (or some middle layer) validates and checks all untrusted (and preferably all trusted) input. All standard database layers (Perl/PHP/Java, whatever) supply tools for this. Your particular issue with \n is just down to PG's standard string parsing - not really related. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to preserve \n in select statement
Hi Richard.. If your users are required to fire only SELECT and no DML, you can do the following: BEGIN; execute the statements given by user ROLLBACK; This will not affect your SELECT and also if any malicious user gives DELETE statement, that will not have any impact too.. HTH Thanx Denis - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Matt Van Mater" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, December 22, 2003 2:34 PM Subject: Re: [SQL] how to preserve \n in select statement > On Saturday 20 December 2003 17:58, Matt Van Mater wrote: > > > > I readily admit that I didn't read much into it, but I think you are > > mistaken. If you look at the following links you will see that postgresql > > definitely had a vulnerability that allowed sql injection through the use > > of escape characters. I was simply saying that this behavior might be a > > way of preventing that from happening again. > > http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802 > > http://cert.uni-stuttgart.de/doc/postgresql/escape/ > > Ah - this "vulnerability" is still there - and, in fact is in every database > ever produced. The issue is that applications using untrusted data to build a > query string can be duped by a malicious user. > > So - say you have a query template: > SELECT * FROM recent_news WHERE topic='?' > > If a malicious user supplies the string: '; DELETE FROM user_logins; SELECT ' > then you will have the resulting query string: > SELECT * FROM recent_news WHERE topic=''; DELETE FROM user_logins; SELECT '' > > If you allow multiple queries in one string, there is no way to prevent this. > If you disallow multiple queries, there are still attacks that are possible. > > This is why it is vital to parse and validate user input. If you are asking > for an integer, check that it is. If you are asking for a string, quotes etc. > need to be escaped (AFAICT the stuttgart url describes a function that lets > you do this - you still need to call it). > > The first URL seems to deal with an old bug that meant a cunning attacker > could bypass your escaping. It is still vital that the application (or some > middle layer) validates and checks all untrusted (and preferably all trusted) > input. All standard database layers (Perl/PHP/Java, whatever) supply tools > for this. > > Your particular issue with \n is just down to PG's standard string parsing - > not really related. > -- > Richard Huxton > Archonet Ltd > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to preserve \n in select statement
On Monday 22 December 2003 09:37, Denis wrote: > Hi Richard.. > > If your users are required to fire only SELECT and no DML, you can do the > following: > > BEGIN; > execute the statements given by user > ROLLBACK; > > This will not affect your SELECT and also if any malicious user gives > DELETE statement, that will not have any impact too.. An interesting idea, though you'd need to be careful with side-effects (triggers/functions etc). I seem to recall a "read-only" setting being discussed for transactions too (though not as a security measure, I should emphasise). The other thing is to use the database user/group mechanism - something which tends to be neglected with web-based apps (partly because different DBs have different setups here). If only an application super-user can add/delete users make sure the permissions reflect this and connect as a more restricted user for other logins. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Get x from point?
On Mon, Dec 22, 2003 at 04:54:01PM +0800, Daniel Lau wrote: > I am wondering if we are able to extract the x-coordinate from a > point-type attribute with SQL. I have been looking for this sort of > functions from documents and websites but it seems the function does not > exist. Or, any altnernate to do so? >From the bottom of the Geometric Functions and Operators chapter of the documentation: "It is possible to access the two component numbers of a point as though it were an array with indices 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate and UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of type box or lseg may be treated as an array of two point values." http://www.postgresql.org/docs/current/static/functions-geometry.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] restoring database
Hi everybody ... i need to restore only one database from a pg_dumpall backup file... how can i do it? Thanks a lot ! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] restoring database
As I am not someone with a lot of experience (depending on the size of the 'dumpall') I would create a new database to suck up the 'all' then pg_dump the table I really cared about. Obviously, if the dump_all is a terabyte database this method is inconvenient (to say the least). However it will work. Ted --- Lucas Lain <[EMAIL PROTECTED]> wrote: > Hi everybody ... i need to restore only one database > from a pg_dumpall backup > file... how can i do it? > > Thanks a lot ! > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] restoring database
On Mon, 22 Dec 2003, Theodore Petrosky wrote: > As I am not someone with a lot of experience > (depending on the size of the 'dumpall') I would > create a new database to suck up the 'all' then > pg_dump the table I really cared about. > > Obviously, if the dump_all is a terabyte database this > method is inconvenient (to say the least). > > However it will work. In the past I've used 'cat -n filename'|grep -4 database to find the starting points of individual databases etc... and then used split to break it into the exact right size pieces to do this. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] how do i get differences between rows
I am after an sql query that can give the differnces between consecutive timestamp values in the resultset. anyone know how to do this? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Returning Setof Record Dynamically
Hi, I am attempting to use Setof Record dynamically. I am developing an application where I will not know what the end-user is trying to work with from a web front end. Thus I will not know before hand the structure of the table I am trying to pull the record from. My Code: CREATE FUNCTION retrievecollection(varchar, varchar, varchar) RETURNS record AS'Declaretablealias ALIAS FOR $1;crmid ALIAS FOR $2;username ALIAs FOR $3;allowed integer;objectdefinition record;realtablename char; beginselect into allowed secverf(username, tablealias);if allowed = 0 then RAISE NOTICE ''User not authorized to perform retrieve.'', allowed; RETURN false;else select into realtablename tablename from applicationaliases where tablealias = qoute_literal(tablealias); if length(crmid) = 0 then FOR objectdefinition IN SELECT * from qoute_ident(realtablename) LOOP RETURN NEXT objectdefinition; END LOOP; else FOR objectdefinition IN SELECT * from qoute_ident(realtablename) where crmid = qoute_literal(crmid) LOOP RETURN NEXT objectdefinition; END LOOP; end if;end if;RETURN record;end;'LANGUAGE 'plpgsql' VOLATILE; I know that I would execute the procedure with select * from retrievecollection as x(x typex, y typey) but how do I do this if I don't know which table was passed to the function? TIA Alex Erwin
[SQL] INDEX and NULL values
Here are the sample tables for my question.
CREATE TABLE bar (
idint2 PRIMARY KEY UNIQUE DEFAULT nextval('bar_id_seq'),
...
);
CREATE TABLE foo (
idint2 PRIMARY KEY UNIQUE DEFAULT nextval('foo_id_seq'),
numberint2 NOT NULL,
bar_idint2 REFERENCES bar ON UPDATE CASCADE ON DELETE CASCADE,
...
);
CREATE UNIQUE INDEX foo_number_id_index ON foo(number, bar_id);
I am a novice for sure. I am trying to prevent multiple combinations
of the number and bar_id fields. However, since the bar_id can be NULL
I am actually getting multiple combinations with NULL.
For example,
foo:
id numberbar_id .
---
1|1|
2|2| 3
3|1|
Row 1 and 3 are duplicates that I wish to disallow. I am however seeing
just this behavior with 7.3.2. I am seeing the duplicates. I am not able
to add another row with number = 2 and bar_id = 3.
What am I doing wrong? What sort of NULLism is biting me?
I have searched around the lists and the documentation, I am sure it is
explained but I am not able to see the solution. I link that helps out
would be appreciated.
Thanks for your help.
Matthew
---
DISCLAIMER: Information contained in this message and/or
attachment(s) may contain confidential information of Zetec, Inc.
If you have received this transmission in error, please notify
the sender by return email.
---
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] testing for null value in integer field?
try: column is null, as manual shows. Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. C. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] restoring database
Hi everybody ... i need to restore only one database from a pg_dumpall backup file... how can i do it? Thanks a lot ! -- Lucas Lain Gerencia de Ingeniería TechTel Telecomunicaciones [EMAIL PROTECTED] TE. (54-11) 4000-3164 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] INHERITS and Foreign keys
Foreign keys, unique and primary key constraints do not meaningfully inherit currently. At some point in the future, that's likely to change, but for now you're pretty much stuck with workarounds (for example, using a separate table to store the ids and triggers/rules on each of the tables in the hierarchy in order to keep the id table in date.) hi same problem here on 7.4 can we vote for this bug somewhere ?! thanks for your time Pedro ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with functions.
Hi, I'n trying to create a function with two input values and returning a SETOF. When I only have one input value everything is working ok. If I instead create the same function with two input values the query fail with: ERROR: a column definition list is required for functions returning "record" The two functions are returning exactly the same type of data. This is the definition of the function with one input value that works ok: CREATE FUNCTION getlosts (timestamp) RETURNS SETOF userlog AS ' DECLARE beg ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM userlog WHERE login > beg LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; This is the definition of the function that fail: (note that I only added the secondary timestamp used to limit the upper value of the select) CREATE FUNCTION getlosts (timestamp, timestamp) RETURNS SETOF userlog AS ' DECLARE beg ALIAS FOR $1; ending ALIAS FOR $2; rec RECORD; BEGIN FOR rec IN SELECT * FROM userlog WHERE login > beg AND login < ending LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; This is the definition of table userlog: Table "public.userlog" Column | Type | Modifiers --+--+--- login| timestamp with time zone | logout | timestamp with time zone | reason | character varying(20)| source | character varying(32)| username | character varying(64)| gw | character varying(32)| ident| character varying(64)| id | integer | I'm running Postgres version 7.4. Thanks /Jonas L. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL]
hi, I am new of postgre sql.im using cursors in pgsql and getting tuples successfully returns in c++, but how can i raise exception in the stored function?.pl anyone help me. This is my function : CREATE or REPLACE FUNCTION sp_test_validatePartnerCode(text) RETURNS int4 AS ' DECLARE t_p_code ALIAS FOR $1; curPartner CURSOR FOR SELECT partner_code FROM test_license WHERE partner_code = t_p_code; BEGIN OPEN curPartner; return curPartner; END; 'LANGUAGE 'plpgsql' ; advance thanks , sundar __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL Optimization
I am working on a query qhich has lot of RIGHT and LEFT Outer joins. This takes almost 2 hours to execute. SELECT mstcurrency.pkcurid, mstcurrency.curswift_code, mstproducts.pkprdid, mstproducts.prdname, mstproducts.fkcnvid_prdbaseuom, cpuser.uspname || ' ' || cpuser.uspsurname as uspname, cpuser.pkuspid, cpuser.uspnotify_number AS cpusercontactdetails, cpuser.fktrdid_usptradhouseid as cpthid, mstcompanies.pkcmpid, mstcompanies.cmpname, mstcompanies.cmpaccount_type, mstfacilityviews.facname, mstfacilityviews.pkfacid FROM mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies ON cpuser.fktrdid_usptradhouseid = mstcompanies.fktrdid_cmptradhouseid AND cpuser.pkuspid = mstcompanies.cmpcontact_userid LEFT OUTER JOIN mstcurrency ON mstcompanies.fktrdid_cmptradhouseid = mstcurrency.fktrdid_curtradhouseid AND mstcompanies.fkcurid_cmpcurid = mstcurrency.pkcurid LEFT OUTER JOIN mstproducts ON mstcompanies.fktrdid_cmptradhouseid = mstproducts.fktrdid_prdtradhouseid AND mstcompanies.cmpmain_product = mstproducts.pkprdid RIGHT OUTER JOIN mstuserprofiles LEFT OUTER JOIN mstfacilityviews ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty AND mstcompanies.fktrdid_cmptradhouseid = mstuserprofiles.fktrdid_usptradhouseid WHERE mstuserprofiles.pkuspid = 101 AND mstuserprofiles.fktrdid_usptradhouseid = 1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Crosstable query
Hello, I'm writing this bit because it adresses a common problem, but uses a different solution. In a simple data model of timeseries, we have a table with fields v (for variable), t (for time) and y (the data value). v,t is the primary key. It is sometimes desireable to have a side-by-side comparison of variables for the same timestamp. so instead of a table t v y --- 1 a 0.5 1 b 1.1 1 c 5.1 2 a 0.6 2 b 1.2 2 c 5.2 3 a 0.7 3 b 1.4 3 c 5.5 we would like a table t ya yb yc - 1 0.5 1.1 5.1 2 0.6 1.2 5.2 3 0.7 1.4 5.5 MS Access has a 'TRANSFORM' function to do this, which transposes the data along a pivot. The solution for PostgreSQL and other databases is to use a so-called self-join; this means that the table is left joined on subqueries of itself, using t as the join field. Each subquery selects a different variable. My solution uses another capability of the database engine: group by and aggregation. I started with the building block case when v = a then y else null end as ya. This selects only the y of records of a, and null otherwise. Using this on the above table I would use select t, case when v = a then y else null end as ya, case when v = b then y else null end as yb, case when v = c then y else null end as yc from thetable; This yields t ya yb yc - 1 0.5 1 1.1 1 5.1 2 0.6 2 1.2 2 5.2 3 0.7 3 1.4 3 5.5 which is almost (but not quite) what we want. The final step is to use the group by construct. You can use min, max or avg for aggregation of the ya's, although it is probably best to create a special aggregation function 'first' or something similar. The final query looks like: select t, min(case when v = a then y else null end) as ya, min(case when v = b then y else null end) as yb, min(case when v = c then y else null end) as yc from thetable group by t; I did a small performance test of my solution against the self-join method, which proved to be a big win. But I did little to optimise either query, and my test set is too small to be reliable. I used version 7.3.4. See for yourself. Regards, Dennis van Dok ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL Optimization
"Preeti Ambardar" <[EMAIL PROTECTED]> writes: > SELECT ... > FROM mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies > ON cpuser.fktrdid_usptradhouseid = mstcompanies.fktrdid_cmptradhouseid > AND cpuser.pkuspid = mstcompanies.cmpcontact_userid > LEFT OUTER JOIN mstcurrency > ON mstcompanies.fktrdid_cmptradhouseid = mstcurrency.fktrdid_curtradhouseid > AND mstcompanies.fkcurid_cmpcurid = mstcurrency.pkcurid > LEFT OUTER JOIN mstproducts > ON mstcompanies.fktrdid_cmptradhouseid = mstproducts.fktrdid_prdtradhouseid > AND mstcompanies.cmpmain_product = mstproducts.pkprdid > RIGHT OUTER JOIN mstuserprofiles > LEFT OUTER JOIN mstfacilityviews > ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid > ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty > AND mstcompanies.fktrdid_cmptradhouseid = > mstuserprofiles.fktrdid_usptradhouseid > WHERE mstuserprofiles.pkuspid = 101 AND mstuserprofiles.fktrdid_usptradhouseid = 1 Ugh :-( 7.4 will do a better job with this than previous releases, but you are still going to be up against the fact that outer joins constrain the join order. See http://www.postgresql.org/docs/7.4/static/explicit-joins.html You may need to revise the query to do the joins in a more appropriate order. One trick to try is to reduce all the outer joins to plain inner joins, then EXPLAIN that form of the query to see what order the planner thinks the tables should be joined in. Then see if you can revise the query to do the outer joins in that order. One thing I find particularly odd is that the query is phrased to cause the entire join of mstuserprofiles and mstfacilityviews to be formed, because the last few lines will be parsed as RIGHT OUTER JOIN (mstuserprofiles LEFT OUTER JOIN mstfacilityviews ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid) ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty AND mstcompanies.fktrdid_cmptradhouseid = mstuserprofiles.fktrdid_usptradhouseid Surely that ordering of the ON clauses was a thinko and should be reconsidered. And do you really want a RIGHT JOIN to the second occurrence of mstuserprofiles? Why? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [JDBC] Insert Row to ResultSet problem....java.sql.SQLException: No Primary Keys
On 16/12/2003 15:24 Jason L. van Brackel wrote: [snip] . this is where I get this exception java.sql.SQLException: No Primary Keys ~~~ at org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1363) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.moveToInsertRow(AbstractJdbc2ResultSet.java:697) at com.cimconsultants.EFRMigration.MigrationUtil.main(MigrationUtil.java:62) [snip] I'm experienced with Java, but very new to JDBC and PostgreSQL. I'm using the JDK 1.4, PostgreSQL 7.4, and the binary pg74jdbc3.jar driver. A total shot in the dark but do you have a primary key on the table? -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Crosstable query
On Dec 19, 2003, at 8:41 AM, Dok, D. van wrote: MS Access has a 'TRANSFORM' function to do this, which transposes the data along a pivot. You'll probably want to look at Joe Conway's tablefunc. It's in contrib. It's included in 7.4; I'm not sure about 7.3.4, but you can add it if it's not. Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
> I am new of postgre sql.im using cursors in pgsql and > getting tuples successfully returns in c++, but how > can i raise exception in the stored function?.pl > anyone help me. You can use 'raise exception' for this purpose. Refer this link for details: http://www.postgresql.org/docs/current/interactive/plpgsql-errors-and-messages.html regards, bhuvaneswaran signature.asc Description: This is a digitally signed message part
[SQL] Pgaccess problem on RedhatLinux9
Hi All, Previously i was used RedhatLinux7.2 & Postgres7.4, that time i'm able to use the pgaccess command to view the tables. Presently, i'm using RedhatLinux9 & Postgres7.4, here i'm not able to use the pgaccess command. It is saying "command not found." One thing, i observed was on RedhatLinux7.2,this pgaccess is available at \usr\share\pgsql\pgaccess.this is missing at redhatlinux9. Is there any way to use pgaccess on Redhatlinux9. Thanks in advance, Vijay _ Contact brides & grooms FREE! Only on www.shaadi.com. http://www.shaadi.com/ptnr.php?ptnr=hmltag Register now! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
