note 107703 added to function.sqlsrv-query

Tip: It may seem obvious, but you need to trim your strings down to fit in the database field you are saving them to via a parametrized query or a stored procedure. (IE: only submit up to 20 characters to a VARCHAR(20) database field). If you send a larger string to the query then it can handle, you will get an error. When cleaning up your strings, you will most likely find yourself using the php substr() function. This function will return, as documented, a boolean FALSE value when presented with an empty string. Not minding this boolean FALSE value will cause "0" to be saved in your database tables instead of an empty string. Since trimming your input is also important, the simple and intuitive solution for this is to trim your substr() output, which will consistently supply and empty string, not boolean FALSE. So this will always work: <?php //trim last returns our empty string as a data type of string $address_line_2 = trim(substr($_POST['addr2']),0,30)); echo gettype($address_line_2); //outputs string //executing a database query will save "" in field tblAddressBook.addr2 $sql = "update tblAddressBook set name=(?), addr1=(?), addr2=(?),..." $params = array($name, $address_line_1, $address_line_2, ...) $sql_srv_query($db_conn, $sql, $params); ?> This second way will give seemingly unexpected data in your database. <?php //if the result of trimming our post variable is "" (empty), substr() will return FALSE $address_line_2 = substr(trim($_POST['addr2'])),0,30); //$address_line_2 actually === FALSE, not "" echo gettype($address_line_2); //outputs boolean //executing a database query will save "0" in field tblAddressBook.addr2 $sql = "update tblAddressBook set name=(?), addr1=(?), addr2=(?),..." $params = array($name, $address_line_1, $address_line_2, ...) $sql_srv_query($db_conn, $sql, $params); ?> You can also cast the type as a string using, which will cast the boolean false back to the expected Empty String. <?php $address_line_2 = (string)substr(trim($_POST['addr2'])),0,30); echo gettype($address_line_2); //outputs string //executing a database query will save "" in field tblAddressBook.addr2 $sql = "update tblAddressBook set name=(?), addr1=(?), addr2=(?),..." $params = array($name, $address_line_1, $address_line_2, ...) $sql_srv_query($db_conn, $sql, $params); ?> I didn't notice this behavior until switching to IIS7, PHP 5.3.8 and SQL Server 2008. But the behavior also is exhibited with IIS7, PHP 5.2 and SQL Server 2008. ---- Server IP: 69.147.83.197 Probable Submitter: 67.107.56.149 ---- Manual Page -- http://www.php.net/manual/en/function.sqlsrv-query.php Edit -- https://master.php.net/note/edit/107703 Del: integrated -- https://master.php.net/note/delete/107703/integrated Del: useless -- https://master.php.net/note/delete/107703/useless Del: bad code -- https://master.php.net/note/delete/107703/bad+code Del: spam -- https://master.php.net/note/delete/107703/spam Del: non-english -- https://master.php.net/note/delete/107703/non-english Del: in docs -- https://master.php.net/note/delete/107703/in+docs Del: other reasons-- https://master.php.net/note/delete/107703 Reject -- https://master.php.net/note/reject/107703 Search -- https://master.php.net/manage/user-notes.php