[GENERAL] This is my first template
Hi folks Looks like there's something I'm not understanding about date/time queries on a date field. I have a param query that includes the statement: AND active_from_date <= $2 where $2 is a well-formed ISO date. Query works as expected when there are records in the result set. When the result set is empty, PG throws an error: date/time field value out of range: "2011-4-31" I'm using the default datestyle setting, which should work here, I think? Experimenting with other settings hasn't helped. This query runs hundreds of times a day and I don't want the error clogging up my logs. But I don't want to suppress the error as the query is business-critical and I need to know if anything goes wrong. I'd welcome suggestions as to how I can get this query to run without throwing a date/time error when the result set is empty. Thanks... -- Geoff Caplan Uviva Ltd begin:vcard fn:Geoff Caplan n:Caplan;Geoff org:Uviva Ltd adr:Dartington Hall;;Foxhole;Totnes;Devon;TQ9 6EB;UK email;internet:ge...@uviva.com tel;work:01803 840 840 x-mozilla-html:FALSE url:http://www.uviva.com version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stuck with a query...
Greg, GS> There's no efficient way to write this in standard SQL. GS> However Postgres has an extension DISTINCT ON that would GS> do it: Works as advertised - many thanks! I'd missed the DISTINCT ON extension... This really is a great list - you've saved me a couple of hours of agony, I suspect. ------ Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Stuck with a query...
Hi folks, Sorry to ask a newbie SQL question but I'm struggling... I have a website clickstream log: request_id session_id sequence_num url 100 xxx 1 /foo 101 xxx 2 /bar 102 xxx 3 /hoo 103 yyy 1 /foo 104 yyy 2 /bar I need to count the number of times each url was an exit page. An exit page is the highest sequence_num for each session_id - 102 & 104 in the example. The report would look like: Exit pages: /foo 4555 /bar 3204 /hoo 2337 etc... Any pointers would be much appreciated! ------ Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Performance critical technical key
Bruno Wolff III wrote: >>> Using a sequence shouldn't be slow. Thanks - that's the main thing I need to know. >>> The main potential problem is that it will make the session IDs >>> guessible if you don't take any other steps. That may or may not >>> be a problem. Thanks for the warning, but I won't be using the sequence number as the session id: as you say, not a safe thing to do. The session record key persists from session to session: it is used to link sessions with browsers and with user accounts. The session key will be a random 32 character key generated for each session. Christopher Browne wrote: >>> Why, in particular, would you expect the sequence to slow down >>> inserts? They don't lock the table. I was assuming that generating the sequence number was expensive: it is some other DBs I have used. That was why I was thinking of providing a unique id via a random string. But a practical test shows that in PG it is pretty fast, so there is not need. >>> Note that if you're really doing a lot of INSERTs in parallel, you >>> might find it worthwhile to configure the sequence to cache some >>> number of entries so that they are pre-allocated and stored in memory >>> for each session (e.g. - for each connection) for quicker access. See >>> the documentation for "create sequence" for more details... I think that would be worthwhile. Thanks for the input, folks. -- Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(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
[GENERAL] Performance critical technical key
Hi folks I'm designing a table to be used for web session management. If all goes well with the project, the table should have 100,000+ records and be getting hammered with SELECTS, INSERTS and UPDATES. The table will need a technical key. The question is, what is the most efficient way to do this? a) Generate a random 24 character string in the application. Very quick for the INSERTs, but will the longer key slow down the the SELECTs and UPDATES? b) Use a sequence. Faster for the SELECTS and UPDATES, I guess, but how much will the sequence slow down the INSERTS on a medium sized record-set? There will probably be 6-8 SELECTs & UPDATEs for each INSERT. I appreciate that I could set up some tests, but I am under the hammer time-wise. Some rule-of-thumb advice from the list would be most welcome. ------ Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Correct escaping of untrusted data
Tom, Belated thanks for the info (I've been away from my desk). Very helpful. ------ Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Sql injection attacks
Hi folks Very instructive thread. Thanks to everyone for the input, and especial thanks to Lincoln Yeoh for his detailed explanation of his approach: a standout post! Sorry for the silence - it's not that I'm unappreciative, just that I've been away from my desk. Tom Lane wrote: > I think you misunderstood. Escaping is perfectly safe (given a correct > escaping function) if it's used on *every* untrustworthy input string. > The argument for the "keep data separate from code" approach is > essentially just that it's easier to be sure you haven't forgotten > anyplace where you need to escape. You are right: I was misunderstanding the nature of the problem. I now appreciate that any effective approach must include both validation and escaping of all untrusted data used in a query, both when it is first passed into the application, and also when it is re-used from the data store. But I'd like to be sure I understand exactly what Tom means by "a correct escaping function"? What are the potential breakout strings you have to check for, and are they all caught by the standard Postgres API escaping functions in PHP, Tcl etc? For example, with SQL Server, it appears you can slip in a single-quote using encodings that are cast to ASCII #39 by the server. Also, what about pg functions like convert()? Could they be used to smuggle in a breakout character? Looked at like this, perhaps robust escaping is not so simple? Any advice much appreciated. -- Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Sql injection attacks
Magnus, Your posting arrived just after I posted my attempt at a summary... With the help of the list, I had already got to the stage that parameterised queries are the way to go. Your post helps confirm that. Now I need to understand the implementation details. Clearly, one option is the PREPARE/EXECUTE statements. But I am not sure I follow you when you say: MH> If you use the parametrised formats, e.g. MH> "SELECT * FROM foo WHERE [EMAIL PROTECTED]" MH> or just "sp_foo" in the case of the stored procedure (the driver will MH> pick up parameter names automatically) Are you saying that "@bar" is part of the Postgres query syntax? If it is, I can't find it in the 7.4 docs. What do you mean by "the parameterised formats"? I would appreciate a clarification and the URL of the relevant documentation. Thanks -- Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Sql injection attacks
Hi folks
Seems we have two schools of thought:
1) The validation/escaping approach, supported by Bill and Jim
2) The "don't mix data with code" approach supported by Peter and
Greg.
As I learn more about the issues, I am increasingly veering towards
the second approach.
Obviously, proper validation is a given for all kinds of reasons. But
the problem with validation/escaping as the primary defense against
injection seems to be that simply escaping would not catch every type
of insertion via strings.
If you are just saving the untrusted string as a value, then the
attacker needs to use a quote or comment to escape out of the value
and alter the query syntax. This is relatively easy to counter, as
Bill suggests.
But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.
In that scenario, if you just concatenate your untrusted data into
your query, it is relatively easy for the attacker to sneak in
AND/OR/UNION type clauses without using quotes. This means that you
have to check strings for attack signatures as well, which is not
trivial if you are going to avoid false positives. And it means that
every single untrusted string has to be screened by someone skilled at
analysing the potential threats and writing the right Regexes. And as
Greg point out, you only have to screw up once and you have opened up
your whole system. Plus, as new attacks are developed, you have to
audit all your code to counter them. This surely can't be the way to
go.
Bill wrote:
BM> I don't see how storing the SQL in some different location is the
BM> correct way to fix anything?
That was my original suggestion, and I agree it was naive. But I don't
think that is what Peter is suggesting. I think the idea is that
parameterised prepared statements compile the SQL statement before the
user input is added via the parameters, making it impossible for user
input to modify the actual SQL statement.
>From the practical point of view, it surely seems easier to adopt a
particular style of programming ("any query using untrusted data MUST
be a parameterised prepared statement") than to analyse each and every
untrusted parameter for attack signatures? Or am I missing something
here?
--
Geoff Caplan
Vario Software
(+44) 121-515 1154
---(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: [GENERAL] Sql injection attacks
Hi folks, Peter Eisentraut wrote: PE> If you use prepared statements (the details of which vary by >> PE> programming language), you should be quite safe. Peter - thanks for the suggestion. You are right: a poorly designed function might simply concatenate the injected code - I hadn't really thought it through. The key seems to be to treat the unsafe string as a value so it can't leak out into the statement, and a parameterised prepared statement would do this effectively, as you suggest. Very elegant... Bill Moran wrote: BM> To protect yourself from SQL injections, just pass all your data through BM> PQescapeString() I'm no expert, but the papers I have been reading suggest that the usual hygene advice such as don't display DB error messages and escape unsafe strings doesn't cover all types of attack. See, for example, this: http://www.net-security.org/article.php?id=571 But so far as I can see, Peter's suggestion should provide a workable robust solution. So thanks again! -- Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Sql injection attacks
Hi folks, I'm new to Postgres and trying to get up to speed on the security issues. There seems to be remarkably little Postgres specific stuff on preventing SQL injection attacks. Most of the online literature is on MS SQL Server. There, the consensus seems to be that the range of potential attacks is so wide that attempting to spot attack signatures in posted data is a doomed enterprise, and that the safest general approach for any dynamically built query is to execute it as a stored procedure. In SQL Server, this reportedly works because the syntax of the query is pre-compiled, and the variables passed in are treated strictly as data and cannot alter the syntax. So any malicious use of "AND", "UNION", ";" etc in submitted data will fail. Can anyone confirm that this would also apply to Postgres Query Language (SQL) functions? The effectiveness of moving the queries into the database against SQL injection attack would seem to depend on the query engine internals. Will using the SQL functions provide the robust protection I am looking for? -- Geoff Caplan Vario Software Ltd (+44) 121-515 1154 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Need for newbie friendly docs (was Newbie struggling...)
Oliver Elphick > There is an enormous amount of background knowledge assumed when > you document an application, and this is necessary, or else every document > would become a Windows-like spoon-feeder, which would spend so much > time on basic stuff that it would never cover the real meat. > > I'm not sure that it is either possible or desirable for PostgreSQL to > attempt to satisfy a newbie's need for basic training in Unix. > Well, I have spent a lot of time writing instructional material, and I think it is a question of the right balance. You obviously have to assume the basics, like file management. An issue like the one I got stuck on, on the other hand, is not so basic (not covered in the thousands of pages of Unix documentation I consulted) and could have been explained with a single line example. In many ways it is a matter of mindset - when experienced people are writing it is difficult for them to visualise the roadblocks that will catch out those with less knowledge. More beginner friendly docs don't need to be much more verbose - it's about testing them and pinpointing the points of difficulty. The docs have room for many pages on how to do a SELECT, which is covered in detail in every SQL primer, so there is surely no reason why setup should not be covered a bit more clearly. Postgres is not so hard to use, but it is a bit of a pig to administer, and the docs are part of the problem. But I do understand that top quality docs require specific skills and resources which it is perhaps unreasonable to expect from an open source project. It will probably take a commercial effort from GreatBridge or a book to improve things. GreatBridge have made a start. I hope they continue to test and develop their docs, and don't regard the job as done... Geoff Caplan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Struggling to change default data location
Hi folks Having a nightmare changing the default data location in 7.1 I uninstalled and re-installed 7.1 on RedHat 6.2 from the rpms with a clean system. With the help of this list, I have set $PGDATA in my shell config file and exported the value. Postgres utilities such as initdb now recognise $PGDATA and I have setup a system at my desired location. initdb does not write a postmaster.opt file at the new location. On system startup, it does write the .opt file to /var/lib/pgsql/data. Editing this file with the new datapath does not change the behaviour below. At this stage - here is what I get... $ /usr/bin/pg_ctl -D /www/dbdata -l logfile start postmaster successfully started $ pg_ctl status pg_ctl: postmaster or postgres is not running $ ps ax | grep postmaster 582 ?S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data Please note: - postmaster is running, but pg_ctl can't find it - postmaster has started with the default datapath and is ignoring $PGDATA I get the same result if I launch automatically during startup. Can anyone please help me make some sense of this? I am losing the will to live... Geoff Caplan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Newbie struggling to set $PGDATA
Hello Steve, Thursday, April 19, 2001, 4:53:58 PM, you wrote: SW> I just upgraded from 7.0.3 to 7.1 (using the RH rpms) with no SW> major problems. Nice! SW> However, I'm getting some strange behaviour from the init SW> script (/etc/rc.d/init.d/postgresql). In particular, a SW> restart when the database is running fails because the SW> "sleep 2" in stop() is no longer long enough (on a dual-cpu SW> PIII/650 machine!). So "pidof postmaster" returns a non-null SW> value and *that* results in a syntax error: SW> Checking postgresql installation: [ OK ] SW> ./postgresql: [: 18209: unary operator expected SW> (I think the test should become something like "if [ -n "$pid" ];..." SW> instead of just "if [ $pid ]", which at least prevents the SW> syntax error...) SW> Of course, fixing the syntax error isn't the main problem, which SW> is that the shutdown of the old postmaster(s) hasn't completed yet, SW> resulting in a "false positive" from the pidof operation. SW> I bumped the "sleep 2" up to "sleep 5" and the problem went away. SW> Surely there's a better fix? Hi Please help out a Linx/Postgres newbie. I simply want to set the $PGDATA environmental variable, but can't figure out how. The docs assume you already know... I have tried setting it in my bash /etc/profile configuration file, and it shows up ok if I "echo $PGDATA" in the shell. But none of the postgres utilities such as "initdb" seem to be able to find it. What don't I understand? I have already checked the docs/GreatBridge manual/faqs/archive, so I would very much appreciate some help. Geoff Caplan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Strange problems with phpPgAdmin
Hi folks My installation of phpPgAdmin has suddenly stopped working. I am not very experienced in this area and have run out of ideas. Any pointers would be much appreciated. THE SYMPTOMS: - To summarise, I get all the forms primed with the initial data from the default DB, but it won't run any queries via links or fields. - Either nothing happens, I get a duplicate LH pane, or I get an error. For example if I click on "select" I get the message: Warning: PostgreSQL query failed: ERROR: Relation '' does not exist in /www/htdocs/phpPgAdmin/tbl_select.php on line 12 PostgreSQL said: ERROR: Relation '' does not exist Your query: SELECT * FROM "" LIMIT 1 WHAT I HAVE CHECKED - I am logging on as the SU who owns all the databases - PHP/Postgres is working fine - I can access the databases via psql and PHP scripts without problems. - I re-intalled phpPgAdmin from scratch. So, advice would be very welcome - it's a great app and I am missing it! Geoff Caplan Caplan Associates PS I did post this to the phpPgAdmin specialist list but without reply. Sorry for the cross posting, but I am very keen to get this fixed. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Strange problems with phpPgAdmin
Hi folks My installation of phpPgAdmin has suddenly stopped working. I am not very experienced in this area and have run out of ideas. Any pointers would be much appreciated. THE SYMPTOMS: - To summarise, I get all the forms primed with the initial data from the default DB, but it won't run any queries via links or fields. - Either nothing happens, I get a duplicate LH pane, or I get an error. For example if I click on "select" I get the message: Warning: PostgreSQL query failed: ERROR: Relation '' does not exist in /www/htdocs/phpPgAdmin/tbl_select.php on line 12 PostgreSQL said: ERROR: Relation '' does not exist Your query: SELECT * FROM "" LIMIT 1 WHAT I HAVE CHECKED - I am logging on as the SU who owns all the databases - PHP/Postgres is working fine - I can access the databases via psql and PHP scripts without problems. - I re-intalled phpPgAdmin from scratch. So, advice would be very welcome - it's a great app and I am missing it! Geoff Caplan Caplan Associates PS I did post this to the phpPgAdmin specialist list but without reply. Sorry for the cross posting, but I am very keen to get this fixed. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
