(') syntax

i got an error because one of my records got (') inside it...

for example i got thr name : j'iono

when i am tring to insert it:
mySQL2="INSERT INTO demo (uname) VALUES ('" &toid& "'')"

i got the error...
what can i do to let records recieve (')

*regular names without (') saved with no problem
*uname set as text field on the Mysql db
sasha85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this instead:

mySQL2="INSERT INTO demo (uname) VALUES (" & chr(34) &toid&  Chr(34) & ")"
0
gamebitsCommented:
You have to escape the ' character with a backslash \' you can use mysql_real_escape_string() function to automate the process (if the variable is coming from a form for example).
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

sasha85Author Commented:
but finnaly i need those (') saved to the field...
0
sasha85Author Commented:
you suggested that i will do:
mySQL2="INSERT INTO demo (mysql_real_escape_string(uname)) VALUES ('" &toid& "'')"
right?
0
Ashish PatelCommented:
Replace ' with two '' like below

mySQL2="INSERT INTO demo (uname) VALUES ('" & Replace(toid, "'", "''") & "')"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gamebitsCommented:
They will if you use mysql_real_escape_string() function, mysql use the ' character in is process as a field delimiter the backslash \ tell mysql not to use it.
0
Ashish PatelCommented:
Also your string had two single quote '' before the ) closing bracket. use the one is suggested and all should be good

mySQL2="INSERT INTO demo (uname) VALUES ('" & Replace(toid, "'", "''") & "')"
0
sasha85Author Commented:
is that the right way:
mySQL2="INSERT INTO demo (mysql_real_escape_string(uname)) VALUES ('" &toid& "'')"

how the blackslash connected here?
0
gamebitsCommented:
uname should be a variable $uname
0
Ashish PatelCommented:
NO if you see there are two single quote just before the closing bracket. Better use this and you are all done.

mySQL2="INSERT INTO demo (uname) VALUES ('" & Replace(toid, "'", "''") & "')"
0
gamebitsCommented:
and by the way this escape thing should be done on every field that a user will fill on a form to prevent sql injection.
0
sasha85Author Commented:
asvforce, i got there 2 brackets-thats by mistkae...that not the real code...
i liked your replace solution...but the real escape new to me and i wanna try that too
0
Ashish PatelCommented:
gaebits, escaping should be done in the value part and also to prevent from sql injection to. So the simple solution to this is to replace incomming values with two single quotes like below.

mySQL2="INSERT INTO demo (uname) VALUES ('" & Replace(toid, "'", "''") & "')"
0
sasha85Author Commented:
i am using asp...not php
mySQL2="INSERT INTO demo (mysql_real_escape_string(uname)) VALUES ('" &toid& "'')"
then its the right way?
0
Ashish PatelCommented:
If you want to use mysql_real_escape_string then use it at the part of the value and not at the column defination part. something like this, but still you will have to replace single quote with two ssingle quote at value end to make sql understand the statement.


mySQL2="INSERT INTO demo (uname) VALUES (mysql_real_escape_string('" & Replace(toid, "'", "''") & "'))"
0
Ashish PatelCommented:
I know you are using asp from the code you presented where & is being used. so try using the above solution and let me know.
0
sasha85Author Commented:
VALUES (mysql_real_escape_string('" & Replace(toid, "'", "''") & "'))
that still gives me a syntax error:(
0
sasha85Author Commented:
and as a matter of fact
VALUES (mysql_real_escape_string('" &toid& "')
too
0
Ashish PatelCommented:
well, i still dont understand that apart from the single quote you dont need anything else to be escaped, so I would rather suggest you to use the simple query without any mysql_real_escape_string function and that will protect you from everything silly. You just need one esapce character to be replace and which we will have to do by this underlying method.

mySQL2="INSERT INTO demo (uname) VALUES ('" & Replace(toid, "'", "''" & "')"
0
sasha85Author Commented:
the thing is that,i want to solve this for all future cases...
here we will solve only the (')
but i am sure there is other chars that will do the same prob?
0
Ashish PatelCommented:
I guess, i have been using sql server since long and there are no other escape characters which you will have to take care about apart from the single quote character.

Rather the best option to insert values in a table is by using adodb command parameters or procedures. There you dont have to worry about any cases or escape characters. By the way the above will also take care of all future cases too.
0
Ashish PatelCommented:
Here is the example of very safe and sound method for inserting values using paramters. Please change YOURCONNECTION with your connection object.

mySQL2="INSERT INTO demo (uname) VALUES (?)"
set cmd = server.createObject("Adodb.Command")
cmd.ActiveConnection = YOURCONNECTION
cmd.CommandText = mySQL2
with cmd
      .parameters(0).value = toid
end with
cmd.execute

0
sasha85Author Commented:
by the way, my original connection is via server.createObject("Adodb.Command")

but that part is new to me:with cmd
      .parameters(0).value = toid
end with

what is the logic of it?
0
z3r3Commented:
I work with a variety of databases and in order to avoid compatibility issues we have found the only way to avoid issues like this is to use parameters for selects, updates and inserts.

This protects you from sql injection where an end user tries to put in additional sql commnds themsleves and it ensures all the characters a user inputs will be correctly saved. The two most important field types to do this on are the CHARACTER/STRING fields and the DATE fields.
0
sasha85Author Commented:
i set all my fields as TEXT...do you think i will have the same problem and upgrade to parameters?
0
z3r3Commented:
the with allows you not reference the whole object so the following are equivalent:

with server.createObject("Adodb.Command")
  .ActiveConnection = YOURCONNECTION
  .CommandText = "INSERT INTO demo (uname) VALUES (?)"
  .parameters(0).value = toid
  .execute
end with

OR

cmd=server.createObject("Adodb.Command")
with cmd
  .ActiveConnection = YOURCONNECTION
  .CommandText = "INSERT INTO demo (uname) VALUES (?)"
  .parameters(0).value = toid
  .execute
end with

OR

cmd=server.createObject("Adodb.Command")
cmd.ActiveConnection = YOURCONNECTION
cmd.CommandText = "INSERT INTO demo (uname) VALUES (?)"
cmd.parameters(0).value = toid
cmd.execute
0
z3r3Commented:
Anywhere you use a field where alphanumeric data resides e.g. char, varchar, text, memo, nchar, nvarchar etc you can have problems and so parameters are highly recommended.

Also if you create the cmd onc in your program and then use it multiple times it will speed up your application if it is doing many insert, updates or queries with the same paramertised SQL statement.
0
Ashish PatelCommented:
>>i set all my fields as TEXT...do you think i will have the same problem and upgrade to parameters?

NO, you wont have any problems, just use that you are all fixed.
0
sasha85Author Commented:
dear asvforce:)
what do you mean by "use that"?
0
z3r3Commented:
he means using paramters you won't have any problems.
0
sasha85Author Commented:
can some one please be kind and explain how that parameters solve the problem?
0
z3r3Commented:
if you use parameters what ever is passed in as a parameter is taken by the database as the item for that field. You when you pass a parameter the database has already checked the sql syntax and prepared the query so you are creating a new query to run, you are passing in values to a pre-prepared query. This means that it needs to be passed through in the same format as the original datatype as this is what the database expects i.e. date fields need to be passed dates not text and char fields neeed to be passed strings/chars. Does this explain what you whant to know?
0
sasha85Author Commented:
ok now i understand , all left is the syntax

i use now
dim mySQLpp3, conntempp3, rstempp3
 mySQLpp3="UPDATE demo SET uname='"&uunm&"',carnum='"&carnu&"',newid="&maxid&",hlimit='"&xtasktime&"',status='"&Estat&"',customer='"&Ecusto&"',fromloc='"&Efroml&"',toloc='"&Etol&"',tasks='"&Etask&"' WHERE id="&Eid
 call updateDatabase(mySQLpp3, rstempp3, "modipepp3xe.asp")
response.redirect "soltek.asp"

i have problems with uname cause when ever "uunm" is a word with ' or " 
it just errors me...

what syntax i need to use for making this field parameter?
0
Jinesh KamdarCommented:
Try this.
mySQLpp3="UPDATE demo SET uname = '" & replace(replace(uunm,"\"","\"\""),"'","''") & "',carnum='"&carnu&"',newid="&maxid&",hlimit='"&xtasktime&"',status='"&Estat&"',customer='"&Ecusto&"',fromloc='"&Efroml&"',toloc='"&Etol&"',tasks='"&Etask&"' WHERE id="&Eid

Open in new window

0
sasha85Author Commented:
error
Microsoft VBScript compilation error '800a03ee'

Expected ')'


mySQLpp3="UPDATE demo SET uname = '" & replace(replace(uunm,"\"","\"\""),"'","''") & "',
----------------------------------------------------------------------------------^

Open in new window

0
sasha85Author Commented:
in the original page it is pointing the bug to
,"'",
0
sasha85Author Commented:
some thing like tthis
mySQLpp3="UPDATE demo SET uname = '" & replace(replace(uunm,"\"","\"\""),"'","''") & "',
----------------------------------------------------------------------^

Open in new window

0
sasha85Author Commented:
sorry...:)
this:
mySQLpp3="UPDATE demo SET uname = '" & replace(replace(uunm,"\"","\"\""),"'","''") & "',
-------------------------------------------------------------------------^

Open in new window

0
z3r3Commented:
to use parameters you'd have done the following:



  cmd=server.createObject("Adodb.Command")
  with cmd
    .ActiveConnection = YOURCONNECTION
    .CommandText = "UPDATE demo SET uname=?, carnum=?, newid=?, hlimit=?, status=?,customer=?,fromloc=?,toloc=?,tasks=? WHERE id=?"
    .parameters(0).value = uunm
    .parameters(1).value = carnu
    .parameters(2).value = maxid
    .parameters(3).value = xtasktime
    .parameters(4).value = Estat
    .parameters(5).value = Ecusto
    .parameters(6).value = Efroml
    .parameters(7).value = Etol
    .parameters(8).value = Etask
    .parameters(9).value = Eid
    .execute
  end with
  set cmd=nothing

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.