eZ Community » Forums » General » eZPostgreSQLDB: Error executing query
expandshrink

eZPostgreSQLDB: Error executing query

eZPostgreSQLDB: Error executing query

Saturday 14 February 2015 7:05:11 pm - 6 replies

Again Hi!

You can't finish one topic that u need to start a new one! :P

I keep getting these errors in my error.log file

[ Feb 14 2015 18:52:28 ] [xx.xxx.xx.xxx] eZPostgreSQLDB:
Error: error executing query: SELECT id, parent_id, main_tag_id, keyword, depth, path_string, modified,       remote_id
                    FROM   eztags WHERE  id='Blog/Sierra Leone': ERROR:  invalid input syntax for integer: "Blog/Sierra Leone"
LINE 2:                     FROM   eztags WHERE  id='Blog/Sierra Leo...
                                                    ^ 
[ Feb 14 2015 18:52:47 ] [xx.xxx.xx.xxx] eZPostgreSQLDB:
Error: error executing query: SELECT id, parent_id, main_tag_id, keyword, depth, path_string, modified,       remote_id
                    FROM   eztags WHERE  main_tag_id IN ( '73', '98', '107', '56', 'Blog/Sierra Leone', '105', '93', '40', '108' )  ORDER BY keyword ASC: ERROR:  invalid input syntax for integer: "Blog/Sierra Leone"
LINE 2: ... WHERE  main_tag_id IN ( '73', '98', '107', '56', 'Blog/Sier...
                                                             ^

Community Project 2014.7 (legacy stack).

Just about that tag, again and again. Any idea why and how to fix it?

Thanks blunk.gif Emoticon

Saturday 14 February 2015 10:35:01 pm

Hello Lo F,

Well at first glance it seems you have two SQL errors caused by your code (or implementation usage) for eZTags (on PostgreSQL, which again is not supported).

I would like to troubleshoot with you but you have made it harder by ignoring my request to create a single commit pull request with your PostgreSQL additions: http://share.ez.no/forums/install...rom-mysql-to-postgresql/comment85737

Instead you just closed your pull request and gave up: https://github.com/ezsystems/eztags/pull/85

Please reconsider completing re-opening the pull pull request (which is possible) and doing the work I suggested.

Continuing, what version of eZTags extension are you using?

Have you made any other customizations to eztags (php or template)?

It seems your problem lies in either bad data input (for as of yet unknown reasons) or bad api usage.

You should be able to trace the sql error to a specific view (in dev) and then to a specific template and then to a specific template operator / PHP code.

What uri does this error happen on a node full view, all page views or an eztags specific module view?

It seems (guess) that the first error comes from the use of the eZTags tag fetch function: https://github.com/ezsystems/ezta...#user-content-tagstag-fetch-function

This error comes from using a string as tag_id param input instead of an id. I would strongly suggest reviewing your usage which is passing invalid input to the fetch function's input param tag id (int, id; single) where one input tag id is a string instead of an id.

This could come from bad tpl code usage or bad input from another source (eztags attribute data itself?).

The second error comes from the use of the eZTags 'TagsAttributeFilter'. This line causes the error at the root level: https://github.com/ezsystems/ezta...lasses/eztagsattributefilter.php#L35

This error is clearly identifiable and is caused clearly by template fetch function input.

Example array input usage: https://github.com/ezsystems/ezta...er-content-extended-attribute-filter

It seems like you use custom templates which make use of eztags fetch functions.

I would strongly suggest reviewing your usage which is passing invalid input to the fetch function's extended attribute filter as tag id (array of ids) where one input tag id is a string instead of an id.

This could come from bad tpl code usage or bad input from another source (eztags attribute data themselves?).

Without being able to do a complete code (usage) and database review (does db contain bad data?) it's very hard to give more specific answers (is it bad data or bad static input usage).

I would recommend doing a detailed eztags fetch input code review. In short your passing bad input. This may come from incorrect usage.

I would also recommend doing a quick database review for eztag db content related to the string, 'Blog/Sierra Leone' as if your fetch input usage is dynamic you may have bad data.

I hope this helps!

Cheers,
Heath

Saturday 14 February 2015 11:29:37 pm

Quote from // Heath :

... ignoring my request to create a single commit pull request with your PostgreSQL additions: http://share.ez.no/forums/install...rom-mysql-to-postgresql/comment85737

Instead you just closed your pull request and gave up: https://github.com/ezsystems/eztags/pull/85

Please reconsider completing re-opening the pull pull request (which is possible) and doing the work I suggested.

Hi Heath! Regarding this, I haven't forgotten it. It's definitely not my habit to live something open (or up) but somehow I couldn't make it (..lack of time, mainly). Shame on me that I didn't know how to make a pull request ;P and to amend the target branch wrongly picked.. so I closed it (not because it's close but just not to mess the repo with a wrong pull request).

I will get back to it, promise (like the other conf post which I temporarily put on a side)! Just bear with me a little while blunk.gif Emoticon

- The ezTags extension version is the one bundled with ez publish Community Project 2014.7.

- No, haven't made any customization to any of the extension files (apart from enabling the DelayedIndexing to its site.ini.append, later commented)

- the api requests have to do with objects of a class that doesn't have an eztag attribute.

-Though, I did import packages of objects with eztags attributes, that at first generated an error, due to the fact that the postgres db didn't have eztags tables post installation. After creating those tables, the import of the packages was successful, even though I had to manually re-enter all the tags as there were none.

Let me do some check before I can keep giving more details.

Modified on Sunday 15 February 2015 12:55:13 am by Lo' F.

Saturday 14 February 2015 11:57:09 pm

Here's the fetch

{def $tag_cloud = eztagscloud(hash('class_identifier', 'article', 'parent_node_id', $node.node_id, 'sort_by', array('count', false()), 'limit', $tag_limit ))}
 
{$tag_cloud}

chose eztagscloud over fetch(tags, tree since I needed to use the count sort_by parameters.

And here's the tagcloud.tpl inside my extension. For each tag, I needed to pass its id to the url as a parameter to allow the filtering of the fetched articles. To allow a multiple selection, had to reset the url variable to keep the previous selected tags too.

{def $tag_object = false()
       $url_params=''
       $sel_tags2=array()
       $nodeurl=$node.url}
{if $sel_tags|not}
    {def $sel_tags=''}
{/if}
 
{foreach $tag_cloud as $tag}
    {set $tag_object = fetch( tags, tag, hash( tag_id, $tag['id'] ) )}
 
    {if $tag_params|count()}
        {if $tag_params|contains($tag['id'])}
            {foreach $sel_tags|explode(',') as $single_tag}
                {if $single_tag|ne($tag_object.id)}
                    {set $sel_tags2=$sel_tags2|append($single_tag)}
                {/if}
            {/foreach}
            {set $url_params=$sel_tags2|implode(',')}
        {else}
            {set $url_params=concat($sel_tags, ',', $tag_object.id)}
        {/if}
    {else}
        {set $url_params=$tag_object.id}
    {/if}
    {if $parent_node}
        {set $nodeurl = $parent_node.url}
    {else}
        {set $nodeurl = $node.url}
    {/if}
    <a href="{concat($nodeurl, '/(tag)/', $url_params)|ezurl('no')}" class="btn bgb{if $tag_params|contains($tag_object.id)} current{/if}" style="font-size: {$tag['font_size']}%" title="{$tag['count']} articoli tagged con la stringa {$tag_object.keyword|wash}">
        {$tag_object.keyword|wash}<span class="fa fa-{if $tag_params|contains($tag_object.id)}close{else}plus{/if} float-right"></span>
    </a>
    {set $sel_tags2=array()}
{/foreach}
 
{undef $tag_object $url_params $sel_tags2 $sel_tags $nodeurl}

and here the fetch using the TagsAttributeFilter

 {def $tag_params=$sel_tags|explode(',')
        $children=fetch('content', 'list', hash('parent_node_id', $node.node_id,
                                                                               'offset', $view_parameters.offset,
                                                                               'sort_by', $sorting_order,
                                                                               'class_filter_type', 'include',
                                                                               'class_filter_array', $classes,
                                                                               'extended_attribute_filter', hash(id, TagsAttributeFilter, params, hash(tag_id, $tag_params)),
                                                                               'limit', $page_limit))}

...

Modified on Sunday 15 February 2015 12:18:04 am by Lo' F.

Sunday 15 February 2015 12:46:41 am

Wait a second...

Yeah, that's definitely it and thank u for leading me there...

{def $tag_params=$sel_tags|explode(',')}
{foreach $tag_params as $tag_param}
    {set $tag_param=$tag_param|int()}
{foreach}

... but there's something weird yet, since I've been trying the filtering things and checking the error.log, none of my actions come up, instead it just keeps appending the error as the one I posted above, the same again and again from the same ip. Looks to me a robot keeping sending this request, could it be that? A url request like so http://www.xxx.it/eng/Blog/(tag)/9,10,Blog/Tagname, 14 ?!

Modified on Sunday 15 February 2015 12:50:54 am by Lo' F.

Sunday 15 February 2015 4:57:36 am

Hello Lo F,

I'm glad your making progress in solving this issue.

While web robots can indeed be frustrating to deal with (ie: variable / incorrect input).

You as the programmer hold the true control when it comes to incorrect variable input via validation! happy.gif Emoticon

You should validate your user input with more template code and only use input that is valid.

I would imagine that would have to do with adding additional template code to iterate through and test your view_parameters variable input and rebuild your user input tag id array input variable used in your fetch code to first test to ensure that the id is in fact an id not a string.

{def $tag_params=$view_parameters.tags|explode(',')
       $tag_params_clean=array()}

{foreach $tag_params as $tag_param}
{if $tag_param|is_integer()}
{set $tag_params_clean=$tag_params_clean|append( $tag_param )}
{/if}
{/foreach}

After using the above code, then you can use the user input safe knowing that it is at least not going to cause the error in question.

Please remember, do not use int operator as it is a conversion operator and not a test operator. Use is_integer operator in an if conditional (as above) instead:

https://doc.ez.no/eZ-Publish/Technical-manual/4.x/Reference/Template-operators/Variable-and-type-handling/is_integer

If you want to test with complete accuracy you can tail both ezp error.log and apache access.log and compare the error.log timestamp entries for sql errors to access.log entries which will contain the complete uri request string with the view_parameters that you should use to test your improved template code with to ensure your completely ending the problem once and for all.

This way regardless of the fact you can not stop a crazy web robot's actions, you can validate it's request input to ensure that your error.log is free of these sql errors happy.gif Emoticon

I hope this helps!

Cheers,
Heath

Sunday 15 February 2015 11:26:35 am

No need to say how thankful I am as u've helped me get rid of those silent but yet very annoying errors.

 {def $tag_params=''
        $tag_params_clean=''}
 {if $view_parameters['tag']}
     {set $tag_params_clean=array()
            $tag_params=$sel_tags|explode(',')}
    {foreach $tag_params as $tag_param}
        {set $tag_param=$tag_param|int()}
        {if $tag_param|is_integer()}
            {if $tag_param|ne(0)}
                {set $tag_params_clean=$tag_params_clean|append($tag_param)}
            {/if}
        {/if}
    {/foreach}
{/if}

{def $children=fetch('content', 'list', hash('parent_node_id', $node.node_id,
                                                                 'offset', $view_parameters.offset,
                                                                 'sort_by', $sorting_order,
                                                                 'class_filter_type', 'include',
                                                                 'class_filter_array', $classes,
                                                                 'extended_attribute_filter', hash(id, TagsAttributeFilter, params, hash(tag_id, $tag_params_clean)),
                                                                 'limit', $page_limit))}

...just had to set the $tag_params_clean to an empty string at first when there's no tag for the $children fetch, and convert the parameter to be scanned by the is_integer operator and then exclude the 0 (alas wrong string parameter request). Lots of if but what matters' scope achieved blunk.gif Emoticon

Modified on Monday 16 February 2015 12:51:54 am by Lo' F.

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from