Marco Bagiacchi Marco Bagiacchi - 29 days ago 10
SQL Question

Query - Between operator on PHP ADODB



I have a form with two datepicker (dd/mm/yyyy).

Code:

<label for="from">From Date:</label>
<input type="text" id="from" name="from">
<label for="to">to Date:</label>
<input type="text" id="to" name="to">


And I try to select this range of data with PHP. But there are an error.


Fatal error: Uncaught exception 'com_exception' with message
'Source: Microsoft OLE DB Provider for ODBC
Drivers
Description: [Microsoft][Driver ODBC Microsoft
Access] Tipi di dati non corrispondenti nell'espressione criterio.' in
C:\Program Files
(x86)\EasyPHP-Devserver-16.1\eds-www\DinamicoWeb\index.php:43 Stack
trace: #0 C:\Program Files
(x86)\EasyPHP-Devserver-16.1\eds-www\DinamicoWeb\index.php(43):
com->execute('SELECT [Id Ord]...') #1 {main} thrown in C:\Program
Files (x86)\EasyPHP-Devserver-16.1\eds-www\DinamicoWeb\index.php on
line 43


So this is my php file (work perfectly without date):

$input=$_POST['input'];
$id=$_POST['id'];
$tipo=$_POST['tipo'];
$numero1=$_POST['numero1'];
$numero2=$_POST['numero2'];
$data1=$_POST['from'];
$data2=$_POST['to'];

if (empty($input)) {
$sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo' OR [Data Ord] BETWEEN '$data1' AND '$data2'";
} else {
$sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Indirizzo] LIKE '%$input%' OR [Ragione Sociale] LIKE '%$input%' OR [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo'" OR [Data Ord] BETWEEN '$data1' AND '$data2'";
}


line 43:

$rs = $con->execute($sql);


Form: (not calculate the number from 1 to 100)

enter image description here

Answer

First your code is an open invitation to SQL injection. You should use parametrized queries.

Then, if the DB is MS Access, and [Data Ord] is date type, in the SQL you should surround your dates with # and not ', and also format them properly as follow : MM/DD/YYYY

Something like this.

$input=$_POST['input'];
$id=$_POST['id'];
$tipo=$_POST['tipo'];
$numero1=$_POST['numero1'];
$numero2=$_POST['numero2'];
$data1=date('m/d/Y', strtotime($_POST['from']));;
$data2=date('m/d/Y', strtotime($_POST['to']));;

if (empty($input)) {
    $sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo' OR [Data Ord] BETWEEN #$data1# AND #$data2#";
} else {
    $sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Indirizzo] LIKE '%$input%' OR [Ragione Sociale] LIKE '%$input%' OR [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo'" OR [Data Ord] BETWEEN #$data1# AND #$data2#";
}

I have no idea how your POST data is formated so you might have to change the way I formatted the dates