PHP: odbc_exec - Manual

downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | conferences | my php.net

search for in the

odbc_execute odbc_errormsg

Last updated: Fri, 26 Jan 2007

view this page in

# odbc_exec

(PHP 3 >= 3.0.6, PHP 4, PHP 5)

odbc_exec -- Prepare and execute a SQL statement

## Description

resource odbc_exec ( resource connection_id, string query_string [, int flags] )

Returns `FALSE` on error. Returns an ODBC result identifier if the SQL command was executed successfully.

odbc_exec() will send an SQL statement to the database server specified by `connection_id`. This parameter must be a valid identifier returned by odbc_connect() or odbc_pconnect().

See also: odbc_prepare() and odbc_execute() for multiple execution of SQL statements.

add a note User Contributed Notes
odbc_exec
bslorence
07-Dec-2006 12:38
`A determined attacker will easily jump through whatever string-escaping hoops you can devise. A better way to protect yourself against SQL injection is to bind your parameters, which requires calling odbc_prepare() and odbc_execute():
<?php
$emp_id = $_GET['emp_id'];
$stmt = odbc_prepare($db_conn, "SELECT pwd FROM employees WHERE emp_id=?");
$res = odbc_execute($stmt, array($emp_id));
?>
This should compile the SELECT statement so that the parameter 'emp_id' is never included as part of your literal SQL query; i.e., it prevents your end users from "contributing" to your code.
But of course even the above is not good enough; see particularly the warning about single-quote-delimited strings in odbc_execute()'s parameters array:
http://www.php.net/manual/en/function.odbc-execute.php
It's paramount that you validate user-supplied parameters, always and everywhere -- and keep in mind that every query-variable from a GET or POST should be considered "user-supplied", even if it's in a hidden input field on the front-end web page and the average browser user has no control over it.
Take the above example, and suppose that the backend database uses a two-byte unsigned numeric field to store 'emp_id'.
In that case, then you must make sure, before you do anything with ODBC, that 'emp_id' is indeed numeric, positive, and <= 65535. If it's not, assume an attack and fail with an ambiguous error message.
If you trust user input without checking it yourself, you're asking to be hacked.
Here's a nice intro to SQL-injection:
http://www.unixwiz.net/techtips/sql-injection.html`
delowing gmail dot com
27-Sep-2006 08:19
`It is easy to inject evil code into SQL statements. This wraps parameters in quotes so they are not executable. In your own stored procedures you can convert the string to numeric as needed.
function sql_make_string($sin){
return "'".str_replace("'","''",$sin)."'";
}
// this may delete all data from MYTABLE
$evil = "734'; DELETE FROM MYTABLE; print 'ha ha";
$sql = "SELECT * FROM MYTABLE WHERE mykey = '$evil'";
$rst = odbc_exec($connection,$sql);
// this probably will not delete the data.
$good = sql_make_string($evil);
$sql = "SELECT * FROM MYTABLE WHERE mykey =".$good
$rst = odbc_exec($connection,$sql);`
denis at i39 dot ru
24-Aug-2006 05:24
`I've got an error on MS SQL Server 2000, when tryed to execute query which grants rights to user to connect to database:
<?
$Query = "
EXEC sp_grantdbaccess ".$_REQUEST['user'].";
EXEC sp_addrolemember 'db_owner','".$_REQUEST['user']."';
";
if (!@odbc_exec($db_Conn, $Query))
$strErrorMessage = odbc_errormsg($db_Conn);
?>
The result was: Executing SQL directly; no cursor.
This query executes fine in MS's sql console. So it seems that problem is in odbc driver.
I've found desicion by removing first "EXEC".
It looks:
<?
$Query = "
sp_grantdbaccess ".$_REQUEST['user'].";
EXEC sp_addrolemember 'db_owner','".$_REQUEST['user']."';
";
if (!@odbc_exec($db_Conn, $Query))
$strErrorMessage = odbc_errormsg($db_Conn);
?>
P.S. Don't remove the second 'exec', 'cos MS returns another error :)`
30-May-2006 05:02
`Wouldn't it be better to use the database itself to find out if an id doesn't exist. eg: SELECT COUNT(id) as idCount FROM [Table] WHERE id = [ID]
Then check if idCount is zero or not.`
joex444 at gmail dot com
21-Sep-2005 02:14
`Maybe not the best way, but here's how I find out if a row doesn't exist:
$row=odbc_fetch_array($result);
$id=$row['id'];
if($id=="") {
//no rows
} else {
//rows
}
Where the 'id' field is a required field in your table...`
30-Aug-2005 06:18
`The following seems counterintuitive to me and so I am constantly getting burned by it. Just thought I'd add a note for anyone else who might also get burned.
if (!odbc_exec("select MyValue from MyTable where Key1='x' and Key2='y'"))
is not a good way to search for the existence of a record with Key1 = x and Key2 = y. The odbc_exec always returns a result handle, even though there aren't any records.
Rather, you must use one of the fetch functions to find out that the record really doesn't exist. This should work:
if (!($Selhand = odbc_exec("select MyValue from MyTable where Key1='x' and Key2='y'"))
|| !odbc_result($Selhand, 1))`
fuadMD at gmail dot com
24-May-2005 12:34
`<?php
// - This is a complete working dynamic example of using:
// odbc_connect, odbc_exec, getting col Names,
// odbc_fetch_row and no of rows. hope it helps
// - your driver should point to your MS access file
$conn = odbc_connect('MSAccessDriver','','');
$nrows=0;
if ($conn)
{
$sql = "select * from $month";
//this function will execute the sql satament
$result=odbc_exec($conn, $sql);
echo "<table align=\"center\" border=\"1\" borderColor=\"\" cellpadding=\"0\" cellspacing=\"0\">\n";
echo "<tr> ";
// -- print field name
$colName = odbc_num_fields($result);
for ($j=1; $j<= $colName; $j++)
{
echo "<th align=\"left\" bgcolor=\"#CCCCCC\" > <font color=\"#990000\"> ";
echo odbc_field_name ($result, $j );
echo "</font> </th>";
}
$j=$j-1;
$c=0;
// end of field names
while(odbc_fetch_row($result)) // getting data
{
$c=$c+1;
if ( $c%2 == 0 )
echo "<tr bgcolor=\"#d0d0d0\" >\n";
else
echo "<tr bgcolor=\"#eeeeee\">\n";
for($i=1;$i<=odbc_num_fields($result);$i++)
{
echo "<td>";
echo odbc_result($result,$i);
echo "</td>";
if ( $i%$j == 0 )
{
$nrows+=1; // counting no of rows
}
}
echo "</tr>";
}
echo "</td> </tr>\n";
echo "</table >\n";
// --end of table
if ($nrows==0) echo "<br/><center> Nothing for $month yet! Try back later</center> <br/>";
else echo "<br/><center> Total Records: $nrows </center> <br/>";
odbc_close ($conn);
}
else echo "odbc not connected <br>";
?>`
james @ php-for-beginners co uk
18-Mar-2005 04:54
`hi all, I managed to get this little snippet working, it's pretty useful if you have long forms to be inserted into a database.
if ( ! empty ( $_POST ) ){
array_pop($_POST);
foreach($_POST as $key => $val){
$columns .= addslashes($key) . ", ";
$values .= "'" . addslashes($val) . "', ";
}
$values = substr_replace($values, "", -2);
$columns = substr_replace($columns, "", -2);
$sql = "INSERT INTO table ($columns) VALUES ($values)";
echo $sql;
$results = odbc_exec($conn, $sql);
if ($results){
echo "Query Executed";
}else {
echo "Query failed " .odbc_error();
}
}
Not the most secure in the world but, speeds up collecting data from large forms.`
mramirez at star-dev dot com
28-Feb-2005 04:23
`Hi, I was trying to execute an stored procedure with
PHP 4.3.10 and MS SQL Server 6.5 using PHP function
"odbc_exec" and ODBC.
The problem was that it returned an ouput parameter
and didn't know the right PHP functions and SQL
syntax to call it.
Finally after looking elsewhere, it worked this way:
<html>
<title>test.php</title>
<body>
<?php
$server = 'myservername';
$database = 'mydatabasename';
$username = 'myusername';
$password = 'mypassword';
$connection_string =
'DRIVER={SQL SERVER};SERVER=' . $server . ';DATABASE=' . $database;
$connection = odbc_connect($connection_string, $username, $password);
$sql = "BEGIN ";
$sql .= " declare @MyOutputValue int ";
$sql .= " execute MyStoredProc @MyOutputValue output select @MyOutputValue ";
$sql .= "END ";
echo '<form>' . chr(13);
echo '<table border="1">' . chr(13) . chr(13);
echo '<tr>';
echo '<td><b>Valor</b></td>';
echo '</tr>';
$query = odbc_exec($connection, $sql);
while(odbc_fetch_row($query))
{
echo '<tr>' . chr(13);
// "odbc_result" = "FieldByNumber(Index)",
// "Index" starts with 1 not 0 !!! :
$returnvalue = odbc_result($query, 1);
echo '<td>' . $returnvalue . '</td>';
echo '</tr>' . chr(13);
echo chr(13);
}
echo '</table>' . chr(13);
echo '</form>' . chr(13);
odbc_free_result($query);
odbc_close($connection);
?>
</body>
</html>
Good Luck.`
Tom Cully <mail at tomcully dot com>
28-Feb-2005 08:22
`MS Access through ODBC doesn't just not like quotes (escape with '' - two single quotes), it also really hates newlines - chr(10)s - in LONGCHAR fields, and maybe in other BINARY based fields as well. Leave in a return character - chr(13) - to get a "new line" when working with LONGCHAR fields in ODBC/MS Access:
Here's a function to do both for you:
function odbc_access_escape_str($str) {
$out="";
for($a=0; $a<strlen($str); $a++) {
if($str[$a]=="'") {
$out.="''";
} else
if($str[$a]!=chr(10)) {
$out.=$str[$a];
}
}
return $out;
}`
d dot soussan at sovereignls dot co dot uk
16-Jan-2005 08:19
`re the note from: sk2xml at gmx dot net 21-Nov-2001 02:15
About [] not working with Access.
if you use [] arount the field name then you must also use them around the table name, thus:
select [table1].[field1] from table1
That is standard Access syntax.
sk2xml's example had:
select table1.[field1] from table1
which will always fail.`
Philip_Neeson at Hotmail dot com
14-Nov-2004 02:20
`I kept getting FATAL: emalloc() errors when using select statements via odbc for MS SQL.
I had no control over the DB as it is a commercial CRM system.
I found that by 1st issuing an SQL query of "set textsize 9999;' within my PHP script is has resolved the issue..
I am running PHP 3.x on a Windows 2000 server that is patched to the max,
I spent hours on this one trying diferent ODBC drivers etc and this simple SQL resolved the issue.`
sameer dot kelkar at gmail dot com
31-Aug-2004 03:26
`$callstore = odbc_exec($conn, "{CALL procedurename('" . $para1 . "','" . $para2 . "',1,'125478')}");
odbc_fetch_row($callstore);
$returnmessage = odbc_result($callstore,1);
echo $returnmessage;`
victor dot kirk at serco dot com
26-May-2004 05:47
`rupix said above:
> it does not work. However if I use single quotes instead of \" the thing runs smoothly
SQL uses 'single quotes' to specify strings, thats why a \" does not work.`
Sean Boulter
21-Apr-2004 05:50
`Update to my previous post... Another way to solve the problem with single quotes (at least in my environment (php 4.3.6, ODBC to MS Access db)), is to edit the php.ini file and set magic_quotes_gpc = On and magic_quotes_sybase = On. Note that this is a global setting, which may or may not be desirable.`
Sean Boulter
21-Apr-2004 05:28
`If a single quote exists within the field specified by your WHERE statement, ODBC fails because of a parsing error. Although it seems intuitive, using \" around the field does not work (\"$var\"). The only solution I found was to replace all single quotes in my field with two single quotes. ODBC interprets the first single quote as an escape character and interprets the second single quote as a literal. Thanks to http://www.devguru.com/features/knowledge_base/A100206.html for this tip.`
christopherbyrne at hotmail dot com
23-Jan-2004 01:33
`When you pass a parameter to an Access procedure all data types (seemingly) can be single quoted and dates do not need (and will not work when) surrounded by #'s.
Hope this helps!`
rob at vendorpromotions dot com
17-Jun-2003 01:29
`This opens select statements 'for update' by default in db2. If you're using db2, you have to tack on 'for read only' at the end to select from SYSCAT.TABLES, for example, without firing an error like
Warning: SQL error: [IBM][CLI Driver][DB2/LINUX] SQL0151N The column "MAXFREESPACESEARCH" cannot be updated. SQLSTATE=42808 , SQL state 42808 in SQLExecDirect
For example :
$query = odbc_exec($conn, "select * from syscat.tables for read only");
odbc_result_all($query);
will work (only for db2). I don't know about other databases.
The select statement will work in the 'db2' command line, but not in php, because of this side effect.`
rupix at rediffmail dot com
05-Apr-2003 07:05
`I tried the following line of code
<?php
$odbc=odbc_connect("pbk", "root","") or die(odbc_errormsg());
$q="insert into pbk values(\"$name\", \"$phone\")";
print $q;
odbc_exec($odbc, $q) or die("<p>".odbc_errormsg());
?>
it does not work. However if I use single quotes instead of \" the thing runs smoothly
thus the following would work
<?php
$odbc=odbc_connect("pbk", "yourworstnightmare","abracadabra") or die(odbc_errormsg());
$q="insert into pbk values('$name', '$phone')";
print $q;
odbc_exec($odbc, $q) or die("<p>".odbc_errormsg());
?>
Also having a user dsn is no good on win2k. Always have a System DSN. I don't know yet what are the implications of the same.`
das_yrch at hotmail dot com
07-Mar-2003 03:17
`I tried this way to see the results of a query and it works!!
$Conn = odbc_connect
("bbdd_usuaris","","",SQL_CUR_USE_ODBC );
$result=odbc_exec($Conn,"select nom from usuaris;");
while(odbc_fetch_row($result)){
for($i=1;$i<=odbc_num_fields($result);$i++){
echo "Result is ".odbc_result($result,$i);
}
}`
miguel dot erill at doymer dot com
23-Jul-2002 03:33
`In a previous contribution it was told that if you're running NT/IIS with PHP 3.0.11 you can use MS Access dbs "stored procedures".
That was right, but if those stores procedures have parameters you have to supply them in the command line like this:
$conn_id = odbc_connect( "odbc_test_db", "","", SQL_CUR_USE_DRIVER );
$qry_id = odbc_do( $conn_id, "{CALL MyQuery(".$param.")}" );`
martin at NOSPAMkouba dot at
05-Feb-2002 08:37
`"[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1."
this not so clear to understand error comes when using access-odbc and a field name can't be found. check for correct spelling of fields.`
lee200082 at hotmail dot com
21-Jan-2002 05:07
`As an addition to the note about square brackets earlier:
Enclosing sql field names in '[' and ']' also allows you to use MS Access reserved words like 'date' and 'field' and 'time' in your SQL query... it seems that the square brackets simply tell Access to ignore any other meaning whatever is inside them has and take them simply as field names.`
mh at maxx-web dot de
06-Jan-2002 09:25
`Ich habe beim Nutzen von odbc_exec($conn, $sql); einen Bug entdeckt.
Wenn das SQL Statement ein reines "update" Statement ist so wird es nicht in die Datenbank geschrieben.
Erst nach dem Aufruf eines erneuten "Select" Statements, werden die Daten des vorigen Update Statements geschrieben.
$sql="select * from UserQuotas";
odbc_exec($conn, $sql);
Ich weiss nicht ob mein System daran Schuld ist oder ob es wirklich ein Fehler ist. Ein Oracle spezifisches "commit" habe ich zumindest nirgends gefunden.`
sk2xml at gmx dot net
21-Nov-2001 06:15
`Problem: Fieldnames in SQL-Statement have blanks and [] don't work!
Solution: Try "" instead
Ex.:
SELECT table2.first, table1.[last name] FROM tabel1, table2 -> don't work
SELECT table2.first, table1.\"last name\" FROM tabel1, table2 -> Try this
PS: Don't forget the espace characters !!!`
david dot geere at nospam dot talk21 dot com
17-Aug-2001 01:08
`Problem: Kept getting FATAL: emalloc() errors when using basic select statements via odbc for informix ....
Solution: Cast the columns to an informix odbc friendly type...I chose varchar(255)
example: select description::varchar(255) from objects;
Don't go through the 4 hours I just went through for this..you :-) me :-(`
cfewer1 at home dot com
15-Aug-2001 02:25
`If you're receiving a 'Syntax error in INSERT INTO ..<snip>.. SQL State 37000 in SQLExecDirect' error, try enclosing the field names between square brackets.
ex:
INSERT INTO whatever ([blah],[who],[what]) VALUES ('blah','blah','blah');
I spent 4 hours tryin to get this insert statement (without the []'s) to work. This seems to have fixed it.
[]'s, apparently according to MS, should be used with table/field names with spaces. Im not sure if this is an MS ODBC thing, or a PHP flaw.
tested with: win32/php4.0.6/apache1.3.20/odbc/mdac2.6sp1`
akchu at at ualberta dot ca
07-Jan-2001 08:41
`ODBC/MS Access Date Fields:
Matching dates in SELECT statements for MS Access requires the following format:
#Y-m-d H:i:s#
for example:
SELECT * FROM TableName WHERE Birthdate = #2001-01-07 00:00:00#
or
SELECT * FROM TableName WHERE Birthdate BETWEEN #2000-01-07 00:00:00# AND #2001-01-07 00:00:00#
This took me forever to figure out.`
vpil at retico dot com
06-Nov-2000 06:24
`Additional links to ODBC_exec:
How to actually write the SQL commands:
http://www.roth.net/perl/odbc/faq/
http://www.netaxs.com/~joc/perl/article/SQL.html
Demystifying SQL
BIG REF MANUAL:
http://w3.one.net/~jhoffman/sqltut.htm
Introduction to Structured Query Language
Covers read, add, modify & delete of data.`
phobo at at at paradise dot net dot nz
02-Nov-2000 06:26
`If Openlink -> MS Access Database fails and gives "Driver Not Capable" error or "No tuples available" warning, use the SQL_CUR_USE_ODBC cursor when using odbc_connect()...
Siggy`
andreas dot brunner at rubner dot com
07-Jul-2000 04:54
`I wanted to access an MSAccess database via ODBC. The connection functioned without problems, but when I placed a SQL statement into my odbc_exec() i always got an error:
Warning: SQL error: [Microsoft][ODBC Driver Manager] Driver does not support that function, SQL state IM001 in SQLSetStmtOption in \\Server\directory/test.php3 on line 19.
Resolved my problem by myself: i simply had to install a new odbc-driver from the microsoft homepage.`
gross at arkana dot de
28-Oct-1999 06:03
`If you're running NT/IIS with PHP 3.0.11 and want to use MS Access dbs with "stored procedures" you can send an ODBC SQL query like:
$conn_id = odbc_connect( "odbc_test_db", "", "", SQL_CUR_USE_DRIVER );
$qry_id = odbc_do( $conn_id, "{CALL MyQuery}" );
This way you don't need to integrate query strings like
SELECT * FROM TblObject WHERE (((TblObject.something) Like "blahblahblah"));
in the php file. You directly call the query "MyQuery" that was generated by MS Access.`
rmkim at uwaterloo dot ca
25-Aug-1999 11:13
`for Win32(NT) and MSAcess 2000, whenever you retrieve a date column/field, php will automatically convert it to 'yyyy/mm/dd hh:mm:ss' format regardless of the style of date you've denoted in Access.
This seems to pose a problem when you exec SELECT, UPDATE, or DELETE queries, but strangley INSERT works fine. I've tried parsing the date into the desired format, but php still yells criteria mismatch.`
add a note
odbc_execute odbc_errormsg

Last updated: Fri, 26 Jan 2007
show source | credits | stats | sitemap | contact | advertising | mirror sites
Copyright © 2001-2007 The PHP Group
All rights reserved.
This mirror generously provided by: Yahoo! Inc.
Last updated: Sat Mar 10 10:36:09 2007 PST