php sql odbc_execute() with LIKE not working

Question

So I have this piece of code that is not returning anything (the echo returns nothing and should be returning two rows):

<?php

include "connection.php";

$cliente = $_POST["cliente"];

$select = "SELECT CLIENTE, NOMCLI FROM CLIX1 WHERE NOMCLI LIKE ? ORDER BY NOMCLI";

$stmt = odbc_prepare($con, $select);

//preparing the array for parameter
$prep_array = array();

$prep_array[] = "'%$cliente%'";

$rs = odbc_execute($stmt, $prep_array);

$nombres = array();

$clienteIDS = array();

//if prepare statement is successful
if($rs)
{
    $i = 0;

    while($row=odbc_fetch_array($stmt)) 
    {
        $cliente_id = trim($row["CLIENTE"]);

        $nombre = utf8_encode(trim($row["NOMCLI"]));

        $nombres[$i] = $nombre;

        $clienteIDS[$i] = $cliente_id;

        $i++;

    }

    echo json_encode($nombres) . "|" . json_encode($clienteIDS);
}

else
{
    echo "error";
}

odbc_close($con);


?>

I know the problem is not the parameter pass on the odbc_execute() because even if I do this, it doesn't return anything(with %mich% it should display two rows):

$rs = odbc_execute($stmt, array("%mich%"));

Do you see anything wrong in this code?

Please let me know and thanks in advance.

UPDATE ------

I made the changes on the code that were suggested on the answer below and I am getting a new error now:

Warning: odbc_execute(): Can't open file %mich%

Where mich is the text entered to search on the database.

I'm not sure why you are preparing a statement for a single item in your array. Why not just do a single SELECT query like: `$select = "SELECT CLIENTE, NOMCLI FROM CLIX1 WHERE NOMCLI LIKE '%{$_POST['cliente']}%' ORDER BY NOMCLI";` — Twisty, Apr 09 '15 at 18:16
Like to prevent sql injections, shouldn't I always be doing prepared statements? When should I do prepared statements and when not? — Erick, Apr 09 '15 at 18:17
There are other ways to prevent SQL Injection. MySQL Example: `$select = sprintf("SELECT CLIENTE, NOMCLI FROM CLIX1 WHERE NOMCLI LIKE '%%%s%%' ORDER BY NOMCLI", mysql_real_escape_string($_POST['cliente'], $con));` — Twisty, Apr 09 '15 at 18:21
maybe you can help me find a way for SQL server as well. I also wonder why my code is not working, if I wanted to use prepared statement — Erick, Apr 09 '15 at 18:22
If you have enabled the MySQL log [linl]https://dev.mysql.com/doc/refman/5.1/en/server-logs.html you should be able to see if your query is executing as you expect. I would start there. — rwhite, Apr 09 '15 at 18:24
@rwhite35 the problem is that I am using Microsoft SQL Server — Erick, Apr 09 '15 at 18:25
Please clarify which SQL Server you are interacting with, MSSQL, MySQL? — Twisty, Apr 09 '15 at 18:25
Try putting single quotes around your LIKE parameter. `LIKE '%mich%'` `$rs = odbc_execute($stmt, array("'%" . $cliente . "%'"));` — Sherman, Apr 09 '15 at 18:26
MSSQL Escaping discussed here: http://stackoverflow.com/questions/574805/how-to-escape-strings-in-sql-server-using-php — Twisty, Apr 09 '15 at 18:26
Both have system logs for ODBC databases. Try searching "enable MS SQL log". — rwhite, Apr 09 '15 at 18:27
mssql escaping is not an option for me. The code is too ugly, as mentioned on the post @Twisty. This code should be working I don't see any errors so it is weird — Erick, Apr 09 '15 at 18:33

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

I found the following that may relate: ODBC prepared statements in PHP

$prep_array = array();
$prep_array[] = "'%$cliente%'";
$rs = odbc_execute($stmt, $prep_array);

I think the Double Quotes might be causing an issue.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 09 '15 at 18:42

Twisty

30,304
2
26
45

1

thanks. Ill check into it in a little bit and Ill definitely let you know if it works – Erick Apr 09 '15 at 18:43
1

Pretty sure I said double quotes were the issue above in the comments. It's cool though, you can take credit for it :) – Sherman Apr 09 '15 at 18:56
@Namrehs you absolutely did. If you feel strongly about it, post the answer and let OP give you the credit it deserves. – Twisty Apr 09 '15 at 19:00
I am about to check it out now if that is the problem and then I will give both the credit if it is correct – Erick Apr 10 '15 at 13:38
It is giving me the following error now: Warning: odbc_execute(): Can't open file %mich% – Erick Apr 10 '15 at 13:44
A comment here: http://php.net/manual/en/function.odbc-execute.php 9 years ago suggests the single quotes get treated as defining a File. Very odd. Change: `$prep_array[] = "'%$cliente%'";` To `$prep_array[] = "%$cliente%";` w/o Single Quotes. – Twisty Apr 10 '15 at 16:23
I would also suggest using the newer SQLSRV module for MSSQL connections: http://php.net/manual/en/function.sqlsrv-query.php – Twisty Apr 10 '15 at 16:26

php sql odbc_execute() with LIKE not working

1 Answers1