(') 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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ashish PatelConnect With a Mentor Commented:
Replace ' with two '' like below

mySQL2="INSERT INTO demo (uname) VALUES ('" & Replace(toid, "'", "''") & "')"
0
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
gamebitsConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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 PatelConnect With a Mentor Commented:
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
 
z3r3Connect With a Mentor Commented:
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
 
z3r3Connect With a Mentor Commented:
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
 
z3r3Connect With a Mentor Commented:
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
 
z3r3Connect With a Mentor Commented:
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 KamdarConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.