Getting error in my LDAP query

Question

Using vb2005 and trying to query LDAP from code behind:

 

Dim prefixText As String = "vg"
Dim cn As New 
Data.OleDb.OleDbConnection("Provider=ADSDSOObject")
Dim cmd As 
Data.OleDb.OleDbCommand = cn.CreateCommand()
cmd.CommandText = "select 
sAMAccountName, displayName, title, userAccountControl from 
'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title 
<> ' ' and (userAccountControl <> '66050' and userAccountControl 
<> '514') and sAMAccountName like '%" & prefixText & "%'"
 
Try
            Dim da 
As New 
Data.OleDb.OleDbDataAdapter(cmd)
            
da.Fill(mySqlDataTable)

Value of my select string is: select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '%vg%'

Getting error:

System.Data.OleDb.OleDbException: 'ADSDSOObject' failed with no error message available, result code: DB_E_ERRORSINCOMMAND(0x80040E14).

Now, if I leave out the "title" and the "sAMAccountName" from my select query it works fine. I am trying to get records where the "title" is not empty and name matches my prefix.

Answer 1

hi Pankaj,
if you are creating dynamic query then at the time of creating sql string you can do following example :

 wherestring+=txt.Replace("'","''"); 

// single quote is passed string in
//first parameter and single quote is written twice '' for second.

Answer 2

Hi

when you are use the single quite you need add extra two quote to string.or else is make to sql injection.

About this please take look,

http://archives.devshed.com/forums/windows-107/sql-injecttion-401471.html

thank you

Answer 3

Thanks

Answer 4

Thanks

Answer 5

Hi you just need to Replace every Single timeWriten Quote to Double Time

suppose you are inserting

string " pankaj's Name "

then you should write String Like

"Pankaj' 's name "     { Dobule time Single Quotes before 's' )

Thats it :)

Better to Write Function That Will Replace Every Single Time Quote to Write it Double time in String

public string CSQ(string str)
{
 
    return Strings.Replace(str, "'", "''");
}


if i Helps you please check "Do you like this answer"

Answer 6

Please see the following two links:
http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F
http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/