MYSQL IN Clause error

Question 1

NewPHP’s Question:

I have used the below code in mysql query:

$all_PIDs=array();
foreach($pID as $p)
{
    $all_PIDs[]=$p->ID;
}
$AIDS=implode(',',$all_PIDs);
$table_tsk  = new Timesheets_Table_Tasks();
$select_tsk = $table_tsk->select()
            ->from($table_tsk, array
              (
                'Total'=>'SUM(timesheets_tasks.Time)',
                'Charged'=>'SUM(timesheets_tasks.Time_Charged)'
              ))
           ->where('timesheets_tasks.ProjectID IN ('.$AIDS.')')
            ;

But using the above code I am getting the following error:

“An error has occured
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘))’ at line 1”

I have added a quotation mark(") for IN clause. But the problem is the query only displays for the first $AIDS number. Could someone help me to clear the error?
Thanks!

Answer

zerkms

It should be specified as:

->where('timesheets_tasks.ProjectID IN (?)', $all_PIDs)

so you’re passing an array of integers, not the comma-separated list of it

Answer

Starx

On your codes the quotes are not part of your MySQL query but only your PHP portion. DO this

$AIDS= "'".implode("','",$all_PIDs)."'";

And then

>where('timesheets_tasks.ProjectID IN ('.$AIDS.')'

Question 2

Artem Gordinsky’s Question:

Artem Gordinsky

I have a website that runs on Zend Framework 1.12. It has a controller named ‘users’. When I enter its name incorrectly — http://mywebsite/userss — I rightfully get an error saying that such controller does not exist. When I add a dot to the end of its name, however:
http://mywebsite/users., an error says that a viewscript called users./index.phtml does not exist. Interesting thing is, it still gets the controller (users) correctly.

I have two questions regarding this matter:

How and why does it ignore a dot at the end, and still gets a controller correctly?
Is there a way to reject such controller names, without any modifications to the framework’s core?

Answer

Starx

Excellent question, but to answer this we have dig the source of Zend Framework and initially back To 2007, a function _formatName() was specially designed to remove such anomalies from the URL name. May be it was earlier than this but I don’t know that.

This particular piece is from Zend Framework 0.1.4 (Historic Right??) 🙂

protected function _formatName($unformatted)
{
    $unformatted = str_replace(array('-', '_', '.'), ' ', strtolower($unformatted));
    $unformatted = preg_replace('[^a-z0-9 ]', '', $unformatted);
    return str_replace(' ', '', ucwords($unformatted));
}

Here you see -, _, and . removed on the very first step.

Even today, this function is set to remove - and . but not the _

Here is current Zend Framework 1.x version of that function

protected function _formatName($unformatted, $isAction = false)
{
    // preserve directories
    if (!$isAction) {
        $segments = explode($this->getPathDelimiter(), $unformatted);
    } else {
        $segments = (array) $unformatted;
    }

    foreach ($segments as $key => $segment) {
        $segment = str_replace($this->getWordDelimiter(), ' ', strtolower($segment));
        $segment = preg_replace('/[^a-z0-9 ]/', '', $segment);
        $segments[$key] = str_replace(' ', '', ucwords($segment));
    }

    return implode('_', $segments);
}

Just like before the URI Segment is clean out in this line

$segment = str_replace($this->getWordDelimiter(), ' ', strtolower($segment));

The function getWordDelimeter() returns an array of array('-', '.');^[line] thus removing them first thing in the URL, which answers your first question. About the second question, you can modify this line and remove the . from it.

protected $_wordDelimiter = array('-', '.');

After this the Despatcher will no longer find the controller or any URI component with . on it.

Question 3

Question by Mikey

Mikey

I’m trying to upload my ZF Project to shared hosting

On my XAMPP, ZF’s index page is located (and I access my page) at http://localhost/ZFprojectname/public

On the shared hosting in the root directory I have installed Joomla.

I want to access my ZF in the manner of http://mywebsite.com/booking/

so in this case, when going to http://mywebsite.com/booking/ I should be accessing ZF’s public folder (as far as I understand).

And, I’d like to put my ZFproject in public_html/somefolderName/

How would you do it?

Answer

Answer by Starx

Starx

Shared hosting do not support defining Document Root path so you can use .htaccess to forward the request to public folder instead.

Create a .htaccess file inside the booking directory with the following rule.

RewriteEngine On

RewriteRule ^.htaccess$ - [F]

RewriteCond %{REQUEST_URI} =""
RewriteRule ^.*$ /public/index.php [NC,L]

RewriteCond %{REQUEST_URI} !^/public/.*$
RewriteRule ^(.*)$ /public/$1

RewriteCond %{REQUEST_FILENAME} -f
RewriteRule ^.*$ - [NC,L]

RewriteRule ^public/.*$ /public/index.php [NC,L]

Question 4

Question by Le Trung Hieu

Le Trung Hieu

I’ve been working on a project and everything’s fine until my partner wants to request to an original URL without rewriting.

For example:
He wants to call to

http://mydomain.com/index.php?module=default&controller=check&action=index

instead of

http://mydomain.com/default/controller/index.

Is there any way that i can make the above URL working?

Answer

Answer by Starx

Starx

Yes, in fact Rob Allen ^{(Author of Zend Framework in Action)} has written an excellent article explain just how to do this.

Zend Framework URLs without mod_rewrite

Question 5

Question by Awais Qarni

Awais Qarni

Hi there I am just testing my own developed application and got a problem. I entered

<script>window.location = "http://www.google.com";</script>

in Zend_Form_Element_Text element. I pressed submit and the value is saved. After saving value I redirect the user to listing and when it redirects to listing, script tag executes and it goes to google.com.

My form element looks like

 $first_name = new Zend_Form_Element_Text('first_name');
 $first_name->setRequired(true)
            ->addFilter('StringTrim')
            ->addValidator('StringLength', false, array(2, $metaData['first_name']['LENGTH']))
            ->setDecorators(array('ViewHelper', 'errors'));

I want to know how can I prevent the user to enter such kind of values? Is there any built in validation or any other way?

Answer

Answer by vascowhite

vascowhite

Well done for testing your app, many people don’t bother. Don’t worry about storing that string in your database it won’t do any harm and changing it may give you problems with other, valid, entries. As vstm says, escape it when you use it.

However, as you are specifically talking about a ‘First Name’ field there is probably some more validation that you can do, such as rejecting any names with a / in them. I’m not aware of any language that has that as part of a name. If there is, I’d love to know how it’s pronounced. You could probably add . = and some others to that list too, but don’t get too carried away.

You should carefully consider every field in your form with regards to what input you would reasonably expect to receive and validate the input accordingly. Anything that doesn’t pass validation is rejected. A string like '<script>window.location = "http://www.google.com";</script>' should certainly never pass validation for a field expecting a person’s name.

Personally, I never filter input. It either passes validation and is accepted, or it doesn’t and is rejected. I can’t make good input out of bad input by filtering it, so it gets rejected and the user is asked to re-enter their data. For example, using a StripTags filter on

<script>window.location = "http://www.google.com";</script>

will leave you with

window.location = “http://www.google.com”;

which is still not a valid name and should be rejected.

Your validation will never work 100% of the time and that is why you should always escape values received from user input before echoing them out to the browser.

Zend Framework has a raft of validators that you could use and don’t forget the validators and filters that PHP has already available for you. Use them properly and you will greatly reduce the risk of malicious input hurting either your application or, more importantly, your users.

Those validators and filters are there for you to use, but neither PHP nor Zend Framework know what kind of data you are expecting, so it is very important that you read the documentation and learn exactly how they work, how to use them and when to use them.

There is an excellent resource at The Web Application Security Project that every web dev should be forced to read on pain of death.

tl;dr
Validate input and escape output.

Answer

Answer by Starx

Starx

You can use filters to restrict input from the user. Read about the filters

There is a filter in Zend Framework called Zend_Filter_StripTags which will give you the option to strip all or selected tags. Here is an example from the site itself.

$filter = new Zend_Filter_StripTags();     
print $filter->filter('<B>My content</B>'); 
As result you will get the stripped content ‘My content’.

On your case the following

$first_name->setRequired(true)
            ->addFilter('StringTrim')
            ->addFilter('StripTags') //Here a add a filter to strip tags
            ->addValidator('StringLength', false, array(2, $metaData['first_name']['LENGTH']))
            ->setDecorators(array('ViewHelper', 'errors'));

Question 6

Question by José Carlos

José Carlos

I have developed a web application with Zend Framework which root is http://www.demo31.com/validacion/demo31/ but when I call that url I’ve got the next error:

Page not found

Request Parameters:

array (
  'controller' => 'validacion',
  'action' => 'demo31',
  'module' => 'default',
)

I want that the values of array would be next:

array (
  'controller' => 'index',
  'action' => 'index',
  'module' => 'default',
)

And my .htaccess is correct.

So, what do I have to do what I want?

Answer

Answer by Starx

Starx

Zend framework normally operates as per routes. If a particular URL is not reaching your code, then you have to configure routes to do that.

    $router = $front -> getRouter();
    $routePage = new Zend_Controller_Router_Route('/:controller/:action', array(
    /*                                             ^ Things to notice
                                                     Only two parameters are 
                                                     asked from the route */
        'controller' => 'default',
        'action'    => 'index',
        'module'    => 'default' //Predefine the module as `default
    ));
    $router -> addRoute('default', $routePage);

Question 7

Question by Guilhem Soulas

Guilhem Soulas

I’m trying to put a ZF website on the Internet which works well on my local machine (WAMP).

But on the Linux server, only the main page can be properly displayed. For the other pages, I’ve got a fatal error:

Fatal error: Uncaught exception
‘Zend_Controller_Dispatcher_Exception’ with message ‘Invalid
controller specified (error)’ in
/var/www/staging/library/Zend/Controller/Dispatcher/Standard.php:248
Stack trace: #0
/var/www/staging/library/Zend/Controller/Front.php(954):
Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http),
Object(Zend_Controller_Response_Http)) #1
/var/www/staging/library/Zend/Application/Bootstrap/Bootstrap.php(97):
Zend_Controller_Front->dispatch() #2
/var/www/staging/library/Zend/Application.php(366):
Zend_Application_Bootstrap_Bootstrap->run() #3
/var/www/staging/public/index.php(26): Zend_Application->run() #4
{main} Next exception ‘Zend_Controller_Exception’ with message
‘Invalid controller specified (error)#0
/var/www/staging/library/Zend/Controller/Front.php(954):
Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http),
Object(Zend_Contr in
/var/www/staging/library/Zend/Controller/Plugin/Broker.php on line
336.

I activated the URL rewriting. I’m using modules. The index.php and application.ini are very basic, we didn’t custumize it.

I suppose that there is something wrong with the configuration… Thanks.

Answer

Answer by Starx

Starx

When deploying application from windows platform to Linux, most typical type of error that can be encountered is due to the filename cases. Linux system are very strict about file name and cases.

The error you are encountering is also probably one of these cases. Check the name of ErrorContainer.php and try to match the name you specify in your route and file system.

Question 8

Question by Ankan Bhadra

Ankan Bhadra

I have downloaded ZendFramework-1.11.11 and it has been extracted and then there is zend folder inside library folder of ZendFramework-1.11.11.And then it is copied and paste in the htdocs of xampp. My Xampp version Xampp win32 1.7.1 or Xampp win32 1.7.7
And Then what to do.To check in browser how to check,like localhost/myzendproject/test1.php
I need step by step guide.And also in test.php what to write for checking and in browser what will be URL.
I need your hand. Pls, guied me step by step
Thank you

Answer

Answer by Starx

Starx

Installing Zend framework does not depend on the system where it will run. Follow the following article for the correct walk-through. It denotes every actions needed to get started.

Installing Zend Framework On Windows

Question 9

Question by Julien

Julien

I have the following situation:
We have a webapp built with Zend Framework 1.10 that is available under www.domain.com/webapp

On the server filesystem, we really also have the webapp deployed in /srv/www/webapp

Now, for some reasons I can’t detail too much, the project manager has requested, now that the app is finished, that each client recieves his own url litteraly.
So we would have:
www.domain.com/webapp/client1
www.domain.com/webapp/client2

Normally, what start after the webapp/ would be the controllers, actions and so forth from zend.

Therefore the question: is there a quick way in apache to create these virtual subdirectories, as in the example, client1, client2 ?

I guess it should be possible with url rewriting ?

Thanks in advance

Answer

Answer by Starx

Starx

Rather than creating virtual directories, this can be solved by creating a specific route with Zend_Route. Assuming, controller as User and the action to pass the name would be view, then

$route = new Zend_Controller_Router_Route(
    'webapp/:username',
    array(
        'controller' => 'user',
        'action'     => 'view',
        'username'   => 'defaultuser'
    )
);

Question 10

Question by Jack

Jack

I have a simple question… How could I render the contents of the default.phtml which is in Project/application/layouts/scripts/default.phtml to a variable, so I can have its html.

In the index controller, with an action and a phtml file named test, this would work:

$html = $this->view->render('index/test.phtml');

But, of course, this does not:

$htmlDefaultLayout = $this->view->render('default.phtml');

Since default.phtml is not inside any controller, I guess.

Is there a good way to do that?

Answer

Answer by drew010

drew010

You can add to the path that Zend_View looks in for views so you could then render the default.phtml file.

Example:

// add the layout directory to the path
$this->view->addScriptPath(APPLICATION_PATH . '/layouts/scripts/');

$htmlDefaultLayout = $this->view->render('default.phtml');

The last path added to the scriptPath in Zend_View are the first to be checked (LIFO).

See View Script Paths.

Answer

Answer by Starx

Starx

You can stop the rendering and grab the output like this:

$this->_helper->layout()->disableLayout();
$this->_helper->viewRenderer->setNoRender(true);
$this->view->addScriptPath(APPLICATION_PATH . '/layouts/scripts/'); //default layout path
$htmlDefaultLayout = $this->view->render('default.phtml');

Tag: zend-framework

MYSQL IN Clause error

NewPHP’s Question:

How to prevent Zend Framework 1 from resolving a controller name with a dot on its end?

Artem Gordinsky’s Question:

Running a Zend Framework Project on a shared server

Question by Mikey

Answer by Starx

How to get original URL from a specific Controller + Action

Question by Le Trung Hieu

Answer by Starx

how to prevent script tags in zend form

Question by Awais Qarni

Answer by vascowhite

Answer by Starx

Zend Framework: Page not found

Question by José Carlos

Answer by Starx

Zend Framework: Fatal error on the server

Question by Guilhem Soulas

Answer by Starx

How to install zend framwork on Xampp version 1.7.1 in windows7 eternity

Question by Ankan Bhadra

Answer by Starx

Zend 1.10 place websites in virtual subdirectories

Question by Julien

Answer by Starx

How capture the default.phtml in a variable inside a controller

Question by Jack

Answer by drew010

Answer by Starx

Please fill the form - I will response as fast as I can!