Ajax/PostgreSQL

Paul M Foster <paulf@quillandmouse.com> writes:

Here's an example: The user wants to enter a bill (accounts payable) into the
system. He first has to pick a vendor. Normally, this would entail a PHP page
that generates a PostgreSQL query. The user would then get a second page with
various vendor information (like number of due days for that vendor), and
various other payable info. But wouldn't it be nice to have vendor information
filled in on the original page, directly after the user picks a vendor?
Theoretically, AJAX might allow something like this. But from what I can see,
it would require PostgreSQL bindings in Javascript, and some way to pass the
data back so that PHP could use it.

I'd do it the reverse: Javascript would call a PHP-enabled URL, PHP would get
the data, return to JS, JS would then populate the form.

This way all your logic is contained within PG and PHP. JS would only be used
to manipulate the interface.

--
Jorge Godoy <jgodoy@gmail.com>

Paul M Foster <paulf@quillandmouse.com> wrote:

I'm doing some massive (internal company) applications using PHP, which
query extensive PostgreSQL tables. This is fine, but obviously it often
requires multiple web pages to get something done. Supposedly, AJAX
promises to make web pages more interactive. But from what I understand,
I'd have to have bindings from Javascript into PostgreSQL to make this
work.

NO. NO. ABSOLUTELY NOT. That is an incredible security risk.
Javascript code runs on the client's browser and should not be allowed to
just send raw queries into a database.

Your javascript code should politely ask your server-side code to do
something, and your server-side code should very, *VERY* carefully inspect
the request before sanitizing, escaping, and *maybe* passing it on to
postgresql.

Here's an example: The user wants to enter a bill (accounts payable)
into the system. He first has to pick a vendor. Normally, this would
entail a PHP page that generates a PostgreSQL query. The user would then
get a second page with various vendor information (like number of due
days for that vendor), and various other payable info. But wouldn't it
be nice to have vendor information filled in on the original page,
directly after the user picks a vendor? Theoretically, AJAX might allow
something like this. But from what I can see, it would require
PostgreSQL bindings in Javascript, and some way to pass the data back so
that PHP could use it.

Have PHP serve as an intemediary between the Javascript on the
client browser and the database on your backend. Make sure your PHP looks at
everything the client is sending across very carefully. Pay special
attention to symbols that are used for quoting and escaping in SQL. Don't
hae the javascript think in terms of tables, just result sets, specific ones
that the PHP feeds it.

- Tyler

The way to handle this is to make your ajax call PHP scripts which
handle your data and return your XML for the Javascript (or HTML).

AJAX makes HTTP requests, it does not talk directly to the database
server.

In essence to do what you're asking you would need a JavaScript
implementation of the pgsql protocol. This would at the *very* least
make for the possibility of security issues with your database
backend, where every client hitting your website would need to be
able to access it.

Hope this helps,

Gavin

On Aug 5, 2006, at 3:42 PM, Paul M Foster wrote:

Show quoted text

I'm doing some massive (internal company) applications using PHP,
which query extensive PostgreSQL tables. This is fine, but
obviously it often requires multiple web pages to get something
done. Supposedly, AJAX promises to make web pages more interactive.
But from what I understand, I'd have to have bindings from
Javascript into PostgreSQL to make this work.

Here's an example: The user wants to enter a bill (accounts
payable) into the system. He first has to pick a vendor. Normally,
this would entail a PHP page that generates a PostgreSQL query. The
user would then get a second page with various vendor information
(like number of due days for that vendor), and various other
payable info. But wouldn't it be nice to have vendor information
filled in on the original page, directly after the user picks a
vendor? Theoretically, AJAX might allow something like this. But
from what I can see, it would require PostgreSQL bindings in
Javascript, and some way to pass the data back so that PHP could
use it.

Is this even possible? Is it being worked on? Is there a different
solution I don't know about? I can see where Javascript can alter
the look of a page, but I can't work out how it would allow
interactive use of a PostgreSQL table.

--
Paul M. Foster

---------------------------(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

Paul M Foster wrote:

I'm doing some massive (internal company) applications using PHP, which
query extensive PostgreSQL tables. This is fine, but obviously it often
requires multiple web pages to get something done. Supposedly, AJAX
promises to make web pages more interactive. But from what I understand,
I'd have to have bindings from Javascript into PostgreSQL to make this
work.

Here's an example: The user wants to enter a bill (accounts payable)
into the system. He first has to pick a vendor. Normally, this would
entail a PHP page that generates a PostgreSQL query. The user would then
get a second page with various vendor information (like number of due
days for that vendor), and various other payable info. But wouldn't it
be nice to have vendor information filled in on the original page,
directly after the user picks a vendor? Theoretically, AJAX might allow
something like this. But from what I can see, it would require
PostgreSQL bindings in Javascript, and some way to pass the data back so
that PHP could use it.

Is this even possible? Is it being worked on? Is there a different
solution I don't know about? I can see where Javascript can alter the
look of a page, but I can't work out how it would allow interactive use
of a PostgreSQL table.

No - AJAX is really talking about the ability to use client-side JavaScript to
request XML documents from a server.

In this case if you want to have a page show vendor information when a user
selects a specific vendor, then your client-side JavaScript would request a
resource on your server that will return that information in XML.

If you're using PHP then the request you might make might be something like:
http:/myserver.com/getVendorInfo.php

The PHP request on the server goes to Postgres and retrieves the appropriate
information, formats it as XML and writes it out like any other page.

The client-side Javascript gets the XML response, uses client-side DOM to decode
the response and changes the page HTML to include the appropriate information
without having the page do a refresh.

Obviously this is often not much faster than getting a different page, and is
one of the fundamental problems with things like AJAX and SOA - each remote
request is remote - it has to go over the internet and suffers not only the
processing time at the database but also the network latency.

But to answer your question - there is *not* coupling between JavaScript and
Postgres. The JavaScript runs on the client, Postgres on the server and PHP as
your intermediary.

Best regards
Pete
--
Peter Wilson
YellowHawk Ltd (http://www.yellowhawk.co.uk)
Server Side XML and Javascript Web Application Server - http://www.whitebeam.org

On Aug 5, 2006, at 3:42 PM, Paul M Foster wrote:

But from what I can see, it would require PostgreSQL bindings in
Javascript, and some way to pass the data back so that PHP could
use it.

No, you should be able to this all within PHP with a AJAX library.
99% of my ajax experience has been with Rails.. and there is no need
to make javascript talk directly to PostgreSQL.

Robby

--
Robby Russell
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting

www.planetargon.com
www.robbyonrails.com

+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4068 [fax]

If you take a peek at the CVS version of phpPgAdmin, you will see the
"autocomplete" functionality for foreign keyed columns when doing
"insert row" or "edit row". You can easily get started with that by
using the pagila database and trying to insert some data into
film_category.

$ cvs -d:pserver:anonymous@phppgadmin.cvs.sourceforge.net:/cvsroot/phppgadmin
login

$ cvs -z3 -d:pserver:anonymous@phppgadmin.cvs.sourceforge.net:/cvsroot/phppgadmin
co -P webdb

Pagila: http://pgfoundry.org/frs/download.php/919/pagila-0.8.0.zip

Files of interest is "autocomplete.php", this was part of my Google SoC project.

Regards,
John

Show quoted text

On 8/5/06, Paul M Foster <paulf@quillandmouse.com> wrote:

I'm doing some massive (internal company) applications using PHP, which
query extensive PostgreSQL tables. This is fine, but obviously it often
requires multiple web pages to get something done. Supposedly, AJAX
promises to make web pages more interactive. But from what I understand,
I'd have to have bindings from Javascript into PostgreSQL to make this work.

Here's an example: The user wants to enter a bill (accounts payable)
into the system. He first has to pick a vendor. Normally, this would
entail a PHP page that generates a PostgreSQL query. The user would then
get a second page with various vendor information (like number of due
days for that vendor), and various other payable info. But wouldn't it
be nice to have vendor information filled in on the original page,
directly after the user picks a vendor? Theoretically, AJAX might allow
something like this. But from what I can see, it would require
PostgreSQL bindings in Javascript, and some way to pass the data back so
that PHP could use it.

Is this even possible? Is it being worked on? Is there a different
solution I don't know about? I can see where Javascript can alter the
look of a page, but I can't work out how it would allow interactive use
of a PostgreSQL table.

--
Paul M. Foster

---------------------------(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

On Sat, Aug 05, 2006 at 08:27:25PM -0300, Jorge Godoy wrote:

Paul M Foster <paulf@quillandmouse.com> writes:

Here's an example: The user wants to enter a bill (accounts payable)
into the system. He first has to pick a vendor. Normally, this would
entail a PHP page that generates a PostgreSQL query. The user would
then get a second page with various vendor information (like number
of due days for that vendor), and various other payable info.
But wouldn't it be nice to have vendor information filled in
on the original page, directly after the user picks a vendor?
Theoretically, AJAX might allow something like this. But from what I
can see, it would require PostgreSQL bindings in Javascript, and
some way to pass the data back so that PHP could use it.

I'd do it the reverse: Javascript would call a PHP-enabled URL, PHP
would get the data, return to JS, JS would then populate the form.

This way all your logic is contained within PG and PHP. JS would only
be used to manipulate the interface.

I'm not using php, but the idea behind it is similar.

I use postgresql, zope and scriptaculous. The autocompleter calls a ZSQL
method, which calls a stored procedure and returns data to the ajax part
of the website. If you give a look in google for scriptaculous examples,
you'll find a lot of them for php working in a similar way: they call a
php page which returns the information for the autocompleter.

In all the examples I've seen there's no input sanitysing (they are all
toy examples) but it is not difficult to implement it.

Hi Paul :

You can use deferred load do this.

I am using tu active /deactive user without open a window and going to other
page and return.
No ajax in this code.

----------------------------------
// This is in the user form...
----------------------------------
<head>
<script type="text/javascript" src="../js/cargadiferida.js"></script>
</head>

<script type="text/javascript">

function AtivaDesativar( sDescricao, nIdUsuario )
{
if ( confirm(" Deseja Ativar / Desativar o Usuário " + sDescricao + " ? " )
)
{
// This make the call to a php file that work with pg.
RealizarPeticao( "usuarios_ativardesativar.php?idusuario=" + nIdUsuario
);
} else
return false;

}

}
</script>

-------------------------------
This is the cargadiferida.js:
-------------------------------
function RealizarPeticao( sPagina )
{

if ( sPagina.trim() == '' )
return '';

// Create a new element script...
var oScript = document.createElement( 'script' );

// Put the source page.
// Some like this "usuarios_ativardesativar.php?idusuario=10";
oScript.src = sPagina;

// Y add the new script tag to the body....
document.body.appendChild( oScript );

}

// This is to chage the image in the list.
function ProcessarRespostaAtivacao( sNomeTag, sClase )
{

if ( sNomeTag.trim() == '' )
return '';

oResultado = document.getElementById( sNomeTag.trim() );
oResultado.className = sClase;

}

----------------------------------------------
This is the php file, usuarios_ativardesativar.php :
----------------------------------------------

<?php
header("Content-type: text/javascript"); // Note that the output is
javascript...

require_once( '../comun/cabecalho.php' );

$sSql = " SELECT CASE WHEN ativo = 1 THEN 0 ELSE 1 END AS ativo ";
$sSql .= " FROM os_usuarios ";
$sSql .= " WHERE idusuairo = {$_REQUEST['idusuario]} ";
$sSql .= " AND idempresa = {$_SESSION['empresa_session']} ";

$rs = $Connection->Execute( $sSql );

if ( !$rs )
Error_Msg( ' Erro : ao Atualizar => ', $Connection->ErrorMsg(), $sSql );

if ( !$rs->EOF )
{
if ( $rs->fields['ativo'] == 0 )
$sClase = 'ativar';
else
$sClase = 'desativar';

$sSql = " UPDATE os_usuarios ";
$sSql .= " SET ativo = {$rs->fields['ativo']} ";
$sSql .= " WHERE idusuario = {$_REQUEST['idusuario']} ";
$sSql .= " AND idempresa = {$_SESSION['empresa_session']} ";

$rs = $Connection->Execute( $sSql );

if ( !$rs )
Error_Msg( ' Erro : ao Atualizar => ', $Connection->ErrorMsg(),
$sSql );

?>
// This is a call to ProcessarRespostaAtivacao, note this is a javascript
funcion.
ProcessarRespostaAtivacao( '<?php echo "cmd{$_REQUEST['idusuario']}"; ?>',
'<?php echo $sClase; ?>' );

<?php } ?>

I test this in Firefox and ie. I have no problem to execute this code.

Alejandro Michelin Salomon
Porto Alegre
Brasil.

-->-----Mensagem original-----
-->De: pgsql-general-owner@postgresql.org
-->[mailto:pgsql-general-owner@postgresql.org] Em nome de Paul M Foster
-->Enviada em: sábado, 5 de agosto de 2006 19:43
-->Para: pgsql-general@postgresql.org
-->Assunto: [GENERAL] Ajax/PostgreSQL
-->
-->
-->I'm doing some massive (internal company) applications using
-->PHP, which
-->query extensive PostgreSQL tables. This is fine, but
-->obviously it often
-->requires multiple web pages to get something done. Supposedly, AJAX
-->promises to make web pages more interactive. But from what I
-->understand,
-->I'd have to have bindings from Javascript into PostgreSQL to
-->make this work.
-->
-->Here's an example: The user wants to enter a bill (accounts payable)
-->into the system. He first has to pick a vendor. Normally, this would
-->entail a PHP page that generates a PostgreSQL query. The
-->user would then
-->get a second page with various vendor information (like
-->number of due
-->days for that vendor), and various other payable info. But
-->wouldn't it
-->be nice to have vendor information filled in on the original page,
-->directly after the user picks a vendor? Theoretically, AJAX
-->might allow
-->something like this. But from what I can see, it would require
-->PostgreSQL bindings in Javascript, and some way to pass the
-->data back so
-->that PHP could use it.
-->
-->Is this even possible? Is it being worked on? Is there a different
-->solution I don't know about? I can see where Javascript can
-->alter the
-->look of a page, but I can't work out how it would allow
-->interactive use
-->of a PostgreSQL table.
-->
-->--
-->Paul M. Foster
-->
-->---------------------------(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
-->