Forum OpenACS Development: Unterminated quoted string in db_dml calls

Collapse
1: Unterminated quoted string in db_dml calls
Posted by Torben Brosten on 07/07/04 07:10 AM
An error log  is showing errors:

Error: Ns_PgExec: result status: 7 message: ERROR:  Unterminated quoted string

..when a particular user posts for searches (product-search) and registration (email-password-2).  The user session appears legit. No hacking the url, and the post urls seemed complete.  I would post edited versions of the post urls here, but I accidently overwrote them just now =(

Is there anything programatically that can be done to check form posts etc to more cleanly intercept potential errors like these?

Collapse
2: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Jade Rubick on 07/07/04 08:17 PM
Is the query using bind vars?
Collapse
3: Re: Unterminated quoted string in db_dml calls (response to 2)
Posted by Torben Brosten on 07/07/04 09:14 PM
Bind vars?  I haven't found a relevant definition of that terminology.

They're standard, unmodified queries from the ecommerce package, aolserver3.x.  I suspect these came from a broken IE6 browser.

Collapse
4: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Jade Rubick on 07/07/04 09:36 PM
Torben, can you post an example query that had problems?
Collapse
5: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Torben Brosten on 07/08/04 07:06 AM

Jade, here's the session built from the error.log with id refs etc changed to protect the innocent. 

[06/Jul/2004:19:29:34][79750.146223104][-conn6-] Error: Ns_PgExec: result status: 7 message: ERROR:  Unterminated quoted string

[06/Jul/2004:19:29:34][79750.146223104][-conn6-] Notice: RP (261.931 ms): error in rp_handler: serving POST /ecommerce/product-search 
	ad_url "/ecommerce/product-search" maps to file "/usr/local/www/kingsolar.net/openacs-4.6.3/packages/ecommerce/www/product-search.adp"
errmsg is Database operation "dml" failed
[06/Jul/2004:19:29:34][79750.146223104][-conn6-] Error: POST http://kingsolar.net/ecommerce/product-search?
referred by "http://kingsolar.com/catalog/mfg/dayton/index.html"
Database operation "dml" failed
    while executing
"ns_pg_bind dml nsdb0 {
      insert into ec_user_session_info 
      (user_session_id, category_id, search_text) 
      values 
      (:user_session_i..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    invoked from within
"db_exec dml $db $full_statement_name $sql"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
            db_exec dml $db $full_statement_name $sql
        }"
    (procedure "db_dml" line 37)
    invoked from within
"db_dml insert_search_text_to_session_info "insert into ec_user_session_info (user_session_id, category_id, search_text) values (:user_session_id, :cat..."
    invoked from within
"if { [value_if_exists user_session_id] > 0 } {
    db_dml insert_search_text_to_session_info "insert into ec_user_session_info (user_session_id, categ..."
    ("uplevel" body line 55)
    invoked from within
"uplevel {
    	  ad_page_contract {
    @param search_text
    @param combocategory_id:optional
    @param usca_p:optional
    
    This page searches..."
    (procedure "code::tcl::/usr/local/www/kingsolar.net/openacs-4.6.3/packag..." line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 2)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
	$handler
      } ad_script_abort val {
	# do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
	rp_serve_abstract_file "$root/$path"
	set tcl_url2file([ad_conn url]) [ad_conn file]
	set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."
...
[07/Jul/2004:03:15:44][79750.135969792][-conn10-] Notice: Huh? redirecting: http://kingsolar.net/ecommerce/shopping-cart
[07/Jul/2004:03:15:44][79750.146161664][-conn4-] Notice: LARS: counter = 0 ; this_row? 0 ; next_row? 1
[07/Jul/2004:03:15:44][79750.146161664][-conn4-] Notice: LARS: counter = 0 ; this_row? 1 ; next_row? 0
[07/Jul/2004:03:15:44][79750.146161664][-conn4-] Notice: LARS: counter = 0 ; this_row? 0 ; next_row? 0
[07/Jul/2004:03:15:49][79750.146424832][-conn27-] Notice: Huh? redirecting: https://kingsolar.net/ecommerce/register/index?return_url=https%3a%2f%2fkingsolar%2enet%2fecommerce%2fcheckout%2done%2dform&http_id=0&user_session_id=89139

[07/Jul/2004:03:16:08][79750.146412544][-conn21-] Notice: Huh? redirecting: https://kingsolar.net/ecommerce/register/bad-password?user_id=491876
...
[07/Jul/2004:03:16:28][79750.146100224][-conn0-] Notice: Security: 1089170188 sec_generate_session_id_cookie setting 2820027, 0.
...
[07/Jul/2004:03:16:49][79750.146157568][-conn1-] Error: Ns_PgExec: result status: 7 message: ERROR:  Unterminated quoted string

[07/Jul/2004:03:16:49][79750.146157568][-conn1-] Notice: RP (109.1 ms): error in rp_handler: serving POST /ecommerce/register/email-password-2 
	ad_url "/ecommerce/register/email-password-2" maps to file "/usr/local/www/kingsolar.net/openacs-4.6.3/packages/ecommerce/www/register/email-password-2.adp"
errmsg is Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
[07/Jul/2004:03:16:49][79750.146157568][-conn1-] Error: POST http://kingsolar.net/ecommerce/register/email-password-2?
referred by "https://kingsolar.net/ecommerce/register/email-password?user_id=491876"
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {
      select email from cc_users where user_id=:user_id
-- and user_state in ('authorized','deleted')
      }"
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    invoked from within
"db_exec 0or1row $db $full_statement_name $sql"
    invoked from within
"set selection [db_exec 0or1row $db $full_statement_name $sql]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
        set selection [db_exec 0or1row $db $full_statement_name $sql]
    }"
    (procedure "db_0or1row" line 22)
    invoked from within
"db_0or1row users_state_authorized_or_deleted "select email from cc_users where user_id=:user_id
-- and user_state in ('authorized','deleted')""
    invoked from within
"if ![db_0or1row users_state_authorized_or_deleted "select email from cc_users where user_id=:user_id
-- and user_state in ('authorized','deleted')"] {..."
    ("uplevel" body line 29)
    invoked from within
"uplevel {
    	  ad_page_contract {
    Verifies the person's answer, whether it's their customized answer
    or their full name

    @author Hiro Iw..."
    (procedure "code::tcl::/usr/local/www/kingsolar.net/openacs-4.6.3/packag..." line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 2)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
	$handler
      } ad_script_abort val {
	# do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
	rp_serve_abstract_file "$root/$path"
	set tcl_url2file([ad_conn url]) [ad_conn file]
	set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."
...
[07/Jul/2004:03:17:21][79750.146225152][-conn7-] Notice: Security: 1089170241 sec_generate_session_id_cookie setting 2750139, 0.
...
[07/Jul/2004:03:18:53][79750.146402304][-conn16-] Notice: Security: 1089170333 sec_generate_session_id_cookie setting 2880031, 0.
...
[07/Jul/2004:03:22:21][79750.146410496][-conn20-] Notice: Security: 1089170541 sec_generate_session_id_cookie setting 2870028, 491876.
[07/Jul/2004:03:22:21][79750.146410496][-conn20-] Notice: Huh? redirecting: https://kingsolar.net/ecommerce/checkout-one-form
..
[07/Jul/2004:03:24:17][79750.146146304][-conn3-] Notice: checkout-one-form-2.tcl,line161: user_id: 491876, order_id: 43235632
[07/Jul/2004:03:24:18][79750.146146304][-conn3-] Notice: Huh? redirecting: https://kingsolar.net/ecommerce/checkout-3.tcl?referer=checkout%2done%2dform%2d2
[07/Jul/2004:03:24:19][79750.139678720][-conn11-] Notice: checkout-3.tcl,line52: order_id:43235632, user_id:491876, user_session_id:89139
...
Collapse
6: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Jade Rubick on 07/08/04 08:16 PM
Torben, I'm not sure exactly what is going on here.

Bind vars are described in the docs, under the database API I believe.

Collapse
7: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Torben Brosten on 07/08/04 09:26 PM
Okay, after greping postgres src, I ran a few tests and can now duplicate the error.

Openacs 5.1 passes the test! =)

Looking forward to updating to 5.1

cheers,

Torben

Collapse
8: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Torben Brosten on 07/08/04 09:28 PM
ps. Jade, I'm now reading the updated docs on how to create a package, including the API. Thanks. This couldn't be at a better time for me.
Collapse
9: Re: Unterminated quoted string in db_dml calls (response to 1)
Posted by Jade Rubick on 07/08/04 09:30 PM
I think Joel wrote the new docs, although I'm not sure. But happy to help.