none
PHP 5.3.3 + MSSQL 2008 + Parameter

    Question

  • Hello, where in the code could be wrong? Displays an "Array".

    $query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,?,?)";
    		$params = array(
    			array($login,null),
    			array($hashed_password,null),
    			array($salt,null),
    			array($username,null,null,SQLSRV_SQLTYPE_NVARCHAR),
    			array($prioritet,null)
    			);
    		$sql = sqlsrv_query($conn,$query,$params) or die(sqlsrv_errors());

    Wednesday, March 30, 2011 7:16 AM

Answers

  • Please try this to see if it works...

    (Note to MSFT - html button and code button *does not work* in Firefox!)

    $username_converted = iconv('UTF-8', 'UTF-16le', $username);
    $query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,?,?)";
    $params_array = array(
    $login,
    $hashed_password,
    $salt,
    array($username_converted, NULL, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_NVARCHAR(100)),
    $prioritet);
    $sql = sqlsrv_query($conn,$query,$params_array) or exit(print_r(sqlsrv_errors(),true));
    echo "No errors<br />\r\n";

    Rob
    • Marked as answer by DimaKGD Tuesday, April 5, 2011 12:06 PM
    Tuesday, April 5, 2011 12:00 PM
  • Try this (or something similar) - is 'Adres' a varchar?  If it is not, you should change your parameter to SQLSRV_SQLTYPE_NVARCHAR, like above, and change the query to ".....LIKE N'%' + ? + N'%'....."

    $search = str_replace(array('%','_','['), array('[%]','[_]','[[]'), $search); // wildcards to literals
    $tsql = "SELECT * FROM TP,Tek WHERE ID = TP_ID AND Dostup1 <> '1' AND Adres LIKE '%' + ? + '%' ORDER BY TP.Abon_nomer, TP.Porjad_nomer";
    $params = array(
    array($search,null,null,SQLSRV_SQLTYPE_VARCHAR('max'))
    );
    $result = sqlsrv_query($conn,$tsql,$params) or exit(print_r(sqlsrv_errors(),true));

    Rob

    • Edited by Robert Johnson Tuesday, April 5, 2011 1:57 PM changed str_replace
    • Marked as answer by DimaKGD Wednesday, April 6, 2011 5:58 AM
    Tuesday, April 5, 2011 1:50 PM

All replies

  • The essence of this, you need to convert data from varchar to nvarchar. Sorry for my bad English.
    Wednesday, March 30, 2011 8:42 AM
  • KimaKGD-

    Can you explain in more details what the problem is? You say "Displays and 'Array'". Is the problem with displaying data? Or is it in the execution of the query?

    Thanks.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Wednesday, March 30, 2011 3:37 PM
    Moderator
  • Query fails, when you type Cyrillic characters displayed inscription Array. Once again sorry for my english.

    So it all works:

    $query = "INSERT INTO tablichka(login,password,salt,username) VALUES (?,?,?,N'".$username."')";
    $sql = sqlsrv_query($conn,$query,array($login,$hashed_password,$salt)) or die(sqlsrv_errors());
    

    Wednesday, March 30, 2011 10:06 PM
  • Where else can you ask this question? Already in several forums have asked this question, nowhere did not receive a reply :((
    • Proposed as answer by yHArtx Friday, January 18, 2013 1:00 PM
    Friday, April 1, 2011 5:39 AM
  • As Brian said, it's not really clear what's wrong, but I'm guessing it's a character encoding problem based on your earlier post.

    Edit: The reason your first post prints "Array" is because sqlsrv_errors() is an array - use this statement to exit and print the array: exit(print_r(sqlsrv_errors(),true)); (print_r displays the array contents).  I prefer the word 'exit' to 'die', they are synonymous :-)

    Your query: "INSERT INTO tablichka(login,password,salt,username) VALUES (?,?,?,N'".$username."')" might only work if the following are true:

    1. Your php source file is encoded in utf-16
    2. Your php source file is encoded in utf-8 and you included 'CharacterSet'=>'UTF-8' in your connection options to sqlsrv_connect()
    3. Your php source file is encoded in the character set of your operating system's non-unicode character set and also the default character set of your sql server.

    So, try this, I hope it works:

    1. Save your PHP file in the UTF-8 character set.
    2. Make sure the text columns of your database table are either NCHAR or NVARCHAR character type.
    3. Run some simple code...:

    $conn = sqlsrv_connect('MyServer', array('CharacterSet'=>'UTF-8', 'Database'=>'MyDatabase', 'UID'=>'MySqlUserId', 'PWD'=>'MyPassword') );

    $params = array($login, $hashed_password, $salt, $username);

    $stmt = sqlsrv_query($conn, "insert into tablichka(login,password,salt,username) values(?,?,?,?)", $params);

     

    If you cannot encode your php files in UTF-8, or your SQL Server columns are of type varchar, you have other options using parameter flags and iconv() to convert from one character set to another. 

    If you can give some more detail about your character sets, the database column types and the error you are receiving I will try to help further.


    Rob
    Friday, April 1, 2011 3:19 PM
  • The problem is that, when writing English letters, everything works well, as soon as I enter Russian in the database questions remain.

    Added the line: 'CharacterSet'=>'UTF-8', Encounter the error:

    Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -40 [code] => -40 [2] => An error occurred translating 
    string for input param 4 to UCS-2: Символ Юникода не имеет сопоставления в конечной многобайтовой кодовой
    странице. [message] => An error occurred translating string for input param 4 to UCS-2: Символ Юникода не имеет
    сопоставления в конечной многобайтовой кодовой странице. ) )

     

     

    Recoded page in UTF-8, still instead of Russian letters are added question marks :(

     

    When the code below:

    $query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,?,?)";
    $params = array(
    array($login,null),
    array($hashed_password,null),
    array($salt,null),
    array($username,null,null,SQLSRV_SQLTYPE_NVARCHAR),
    array($prioritet,null)
    );
    $sql = sqlsrv_query($conn,$query,$params) or exit(print_r(sqlsrv_errors(),true));

     

     

    Error:

     

    Array ( [0] => Array ( [0] => 07002 [SQLSTATE] => 07002 [1] => 0 [code] => 0 [2] => 
    [Microsoft][SQL Server Native Client 10.0]ЌҐЇа ўЁ«м­®Ґ Ї®«Ґ COUNT Ё«Ё ®иЁЎЄ бЁ­в ЄбЁб [message] =>
    [Microsoft][SQL Server Native Client 10.0]ЌҐЇа ўЁ«м­®Ґ Ї®«Ґ COUNT Ё«Ё ®иЁЎЄ бЁ­в ЄбЁб ) )

     

     

    The desired result is just like this:

     

    $query = "INSERT INTO tablichka(login,password,salt,username) VALUES (?,?,?,N'".$username."')";<br/>
    $sql = sqlsrv_query($conn,$query,array($login,$hashed_password,$salt)) or exit(print_r(sqlsrv_errors(),true));<br/>

     

     

    Table structure:

     

    id - smallint
    login - nvarchar
    password - nvarchar
    salt -nchar
    username - nvarchar
    



    Friday, April 1, 2011 4:57 PM
  • It just means that $username is not properly encoded in UTF-8.

    May I ask how the variable $username is being populated?

    Also you only need to supply your parameters like this, there is no need to pass each one as an array unless you want to direct the driver to do something other that its default:

    $params = array($login, $hashed_password, $salt, $username, $prioritet);


    Rob
    Monday, April 4, 2011 10:18 AM
  • The site worked with MySQL, changed for work with MSSQL, so mistakes are possible:

     

    <?php
    session_start();
    require ("look.php");
    
    
    include ('module/mysql.php');
    
    /*
    ** Функция для генерации соли, используемоей в хешировании пароля
    ** возращает 3 случайных символа
    */
    
    function GenerateSalt($n=3)
    {
    	$key = '';
    	$pattern = '1234567890abcdefghijklmnopqrstuvwxyz.,*_-=+';
    	$counter = strlen($pattern)-1;
    	for($i=0; $i<$n; $i++)
    	{
    		$key .= $pattern{rand(0,$counter)};
    	}
    	return $key;
    }
    
    if (empty($_POST))
    {
    	?>
    	
    	<h3>Введите данные потребителя</h3>
    	
    	<form action="add_user.php" method="post">
    		<table>
        	<tr>
    				<td colspan="2"><font size="2">Введите название потребителя:</font></td>
    			</tr>
        	<tr>
    				<td>Потребитель:</td>
    				<td><input type="text" name="username" /></td>
    			</tr>
          <tr>
    				<td colspan="2"><font size="2" color="#FF0000">Длина логина должна быть не менее 2х символов!!!</font></td>
    			</tr>
    			<tr>
    				<td>Логин:</td>
    				<td><input type="text" name="login" /></td>
    			</tr>
          <tr>
    				<td colspan="2"><font size="2" color="#FF0000">Длина пароля должна быть не менее 6 символов!!!</font></td>
    			</tr>
    			<tr>
    				<td>Пароль:</td>
    				<td><input type="password" name="password" /></td>
    			</tr>
          <tr>
    				<td colspan="2"><font size="2" color="#FF0000">ВНИМАНИЕ!!! ВАЖНЫЙ ПУНКТ!!!</font></td>
    			</tr>
          <tr>
    				<td>Приоритет:</td>
    				<td><select name="prioritet"><option value="3" selected>По паролю</option>
    <option value="2">Полный доступ</option><option value="1">Администратор</option></select></td> </tr> <tr> <td></td> <td><input type="submit" value="Добавить" /></td> </tr> </table> </form> <?php } else { // обрабатывае пришедшие данные функцией mysql_real_escape_string перед вставкой в таблицу БД /* $login = (isset($_POST['login'])) ? mysql_real_escape_string($_POST['login']) : ''; $password = (isset($_POST['password'])) ? mysql_real_escape_string($_POST['password']) : ''; $username = (isset($_POST['username'])) ? mysql_real_escape_string($_POST['username']) : ''; $prioritet = (isset($_POST['prioritet'])) ? mysql_real_escape_string($_POST['prioritet']) : '';*/ // проверяем на наличие ошибок (например, длина логина и пароля) $error = false; $errort = ''; if (strlen($login) < 2) { $error = true; $errort .= 'Длина логина должна быть не менее 2х символов.<br />'; } if (strlen($password) < 6) { $error = true; $errort .= 'Длина пароля должна быть не менее 6 символов.<br />'; } // проверяем, если юзер в таблице с таким же логином $query = "SELECT id FROM userlist WHERE login= ?"; $sql = sqlsrv_query($conn,$query,array($login)) or die(sqlsrv_errors()); $row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC); if ($row != null) { $error = true; $errort .= 'Пользователь с таким логином уже существует в базе данных, введите другой.<br />'; } // если ошибок нет, то добавляем юзаре в таблицу if (!$error) { // генерируем соль и пароль $salt = GenerateSalt(); $hashed_password = md5(md5($password) . $salt); $query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,N'".$username."',?)"; /* $params_array = array( array($login,null), array($hashed_password,null), array($salt,null), array($username,null,null,SQLSRV_SQLTYPE_NVARCHAR), array($prioritet,null) );*/ /*$sql = sqlsrv_query($conn,$query,$params) or exit(print_r(sqlsrv_errors(),true));*/ $sql = sqlsrv_query($conn,$query,array($login,$hashed_password,$salt,$username,$prioritet)) or exit(print_r(sqlsrv_errors(),true)); /*$sql = sqlsrv_query($conn,$query,array($login,$hashed_password,$salt,$prioritet)) or exit(print_r(sqlsrv_errors(),true));*/ print '<h4>Потребитель <b>'.$username.'</b> с логином <b>'.$login.'</b> успешно добавлен!</h4>'; } else { print '<h4>Возникли следующие ошибки</h4>' . $errort; } } ?>

    String $username = iconv("CP1251", "UTF-8", $username); not helped 

     


    Monday, April 4, 2011 2:04 PM
  • Thanks, I think we are getting somewhere...

    You still don't show where $username is being assigned, but let's assume:
    "$username = $_POST['username'];" ****

    MySQL was probably storing the text as-is and returning it without translating - basically a binary save and restore.

    What encoding are you telling the browser your page is in?  Have a look at these areas:

    1. PHP header, e.g. before you output any content to the browser, run this from php:
      header('Content-Type: text/html; charset=utf-8');
    2. HTML Content type - this tag can go somewhere inside your HTML <head>...</head> tags:
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    3. FORM accept-charset - your form tag can indicate what type of encoding you accept from the browser:
      <form action="add_user.php" method="post" accept-charset="utf-8">

    With all of these settings, you can assume you will receive your $_POST data encoded in UTF-8.  You may choose any character set, but try to be consistent.  If you don't want to encode in UTF-8, your iconv statement will work as long as the text has a mapping between the selected character sets, and you choose the correct encoding of the source text. If you use iconv, you have to specify the parameter like this:
    array($username,null,SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY))

    I have just run a test using some of your Russian text from above, and it all worked for me.

    **** When you embed '$username' in your SQL Statement, without checking for SQL quotes, your site could be broken into.  If you want to embed $username, do this first:
    $username=str_replace("'","''",$username);

    It's a weird looking statement with quotes inside quotes, so paste it into your php editor and it will make more sense.  SQL Server escapes the single quote as 2 single quotes.


    Rob
    • Edited by Robert Johnson Monday, April 4, 2011 4:12 PM iconv parameter information added SQLSRV_ENC_BINARY
    Monday, April 4, 2011 3:32 PM
  • Page encoding me, do as you said, added a line accept-charset = "utf-8", but without success.

    When you type the words "Владимир", this word is recoded in the following form: Владимир

    But the database is stored as: ?’?»?°??????????

    If the record does not add a parameter with the symbol N' ', it remains as is:   Р’ладимир

     

    <strong>$query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,N'".$username."',?)";
    $sql = sqlsrv_query($conn,$query,array($login,$hashed_password,$salt,$prioritet)) or exit(print_r(sqlsrv_errors(),true));</strong>
    

    Maybe it would have decided my problem, but I have an error for some reason - http://msdn.microsoft.com/en-us/library/cc626305%28v=SQL.90%29.aspx

    $query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,?,?)";
    		$params_array = array(
    			array($login,null),
    			array($hashed_password,null),
    			array($salt,null),
    			array($username,null,null,SQLSRV_SQLTYPE_NVARCHAR),
    			array($prioritet,null)
    			);
    		$sql = sqlsrv_query($conn,$query,$params_array) or exit(print_r(sqlsrv_errors(),true));
    

    Error:

    Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => 
    -17 [code] => -17 [2] => An invalid SQL Server type for parameter 
    4 was specified. [message] => An invalid SQL Server type for 
    parameter 4 was specified. ) ) 
    

     

    • Marked as answer by DimaKGD Tuesday, April 5, 2011 12:06 PM
    • Unmarked as answer by DimaKGD Tuesday, April 5, 2011 12:06 PM
    Tuesday, April 5, 2011 6:50 AM
  • Please try this to see if it works...

    (Note to MSFT - html button and code button *does not work* in Firefox!)

    $username_converted = iconv('UTF-8', 'UTF-16le', $username);
    $query = "INSERT INTO userlist (login,password,salt,username,prioritet) VALUES (?,?,?,?,?)";
    $params_array = array(
    $login,
    $hashed_password,
    $salt,
    array($username_converted, NULL, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_NVARCHAR(100)),
    $prioritet);
    $sql = sqlsrv_query($conn,$query,$params_array) or exit(print_r(sqlsrv_errors(),true));
    echo "No errors<br />\r\n";

    Rob
    • Marked as answer by DimaKGD Tuesday, April 5, 2011 12:06 PM
    Tuesday, April 5, 2011 12:00 PM
  • !!!!!Thank you very much Robert!!!!!

    Works even without transcoding, I forgot to specify the size of the field: SQLSRV_SQLTYPE_NVARCHAR('max')

    Tuesday, April 5, 2011 12:05 PM
  • Prompt please as through parameter to organize search in any letter?

    The code with an error:

    $tsql = "SELECT * FROM TP,Tek WHERE ID = TP_ID AND Dostup1 <> '1' AND Adres LIKE <strong>%?%</strong> ORDER BY TP.Abon_nomer, TP.Porjad_nomer";
    	 $params = array(
    	 array($search,null,null,SQLSRV_SQLTYPE_VARCHAR('max'))
    	 );
         $result = sqlsrv_query($conn,$tsql,$params) or exit(print_r(sqlsrv_errors(),true));
    

    Tuesday, April 5, 2011 1:11 PM
  • Try this (or something similar) - is 'Adres' a varchar?  If it is not, you should change your parameter to SQLSRV_SQLTYPE_NVARCHAR, like above, and change the query to ".....LIKE N'%' + ? + N'%'....."

    $search = str_replace(array('%','_','['), array('[%]','[_]','[[]'), $search); // wildcards to literals
    $tsql = "SELECT * FROM TP,Tek WHERE ID = TP_ID AND Dostup1 <> '1' AND Adres LIKE '%' + ? + '%' ORDER BY TP.Abon_nomer, TP.Porjad_nomer";
    $params = array(
    array($search,null,null,SQLSRV_SQLTYPE_VARCHAR('max'))
    );
    $result = sqlsrv_query($conn,$tsql,$params) or exit(print_r(sqlsrv_errors(),true));

    Rob

    • Edited by Robert Johnson Tuesday, April 5, 2011 1:57 PM changed str_replace
    • Marked as answer by DimaKGD Wednesday, April 6, 2011 5:58 AM
    Tuesday, April 5, 2011 1:50 PM
  • I can not understand, adding an entry in the database, it can not find it, but those that were added before using Management Studio, is looking for.
    Wednesday, April 6, 2011 6:29 AM
  • If you use Visual Basic, then everything is fine is sought, why the driver of such a problem?
    Incidentally, the amendment, the driver makes the search, but only the first two letters of the word. If you enter more than 2 letters, finds nothing.
    Thursday, April 7, 2011 8:24 AM
  • Hi

    I can't answer your last 2 questions specifically.  I can assert that the driver works, and php works, but you have to make adjustments when converting code from VB to PHP.

    VB, .Net and COM all have unicode strings (UCS-2/UTF-16), so drivers for these platforms know how to convert the string correctly - this is thanks to Windows excellent multilingual features, since the original design of Windows NT.

    A php string is a binary byte stream, and you encode/decode the contents from your knowledge of where the string came from (e.g. UTF-8 encoded form data sends a utf-8 string), or where it's going to (if sending a utf-8 web page to a browser, you tell the browser to expect utf-8, and encode the string with utf-8 bytes).

    Please try the following code.  This topic's going on a bit, so I'm going to make it my last post, I hope it helps:

    $search = str_replace(array('%','_','['), array('[%]','[_]','[[]'), $search); // wildcards to literals
    $search = iconv('UTF-8', 'UTF-16le', $search);
    $tsql = "SELECT * FROM TP,Tek WHERE ID = TP_ID AND Dostup1 <> '1' AND Adres LIKE N'%' + ? + N'%' ORDER BY TP.Abon_nomer, TP.Porjad_nomer";
    $params = array( array($search, NULL, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_NVARCHAR(100)) );
    $result = sqlsrv_query($conn,$tsql,$params) or exit(print_r(sqlsrv_errors(),true));

    Rob
    Thursday, April 7, 2011 12:21 PM
  • Problem has solved, all because of my silly errors!
    Robert Johnson, I want to tell to you the big human thanks if not you, I wouldn't achieve the necessary result!
    Friday, April 8, 2011 6:33 AM