Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Remove an ' from the insert statement 1

Status
Not open for further replies.
rjn2001

rjn2001

Programmer
Dec 29, 2004
172
0
0
GB
Hi,

I think I need to use a Remove() function or something to remove any random ' in a string,

e.g. ''O'Loughlin', 'niffib', '9', '0', 'fghjk', '£10000')'

from the "O'"

Any ideas?

Cheers Richard

Richard Noon
 
Sort by date Sort by votes
This will remove all single quotes from myString...
Code: 
Replace(myString, "'", "")

Tony
[red]_________________________________________________________________[/red]
Webmaster -
 
Upvote 0 Downvote
Hi,

Thanks alot!, veyr prompt! :)
Can I just ask where i put that?

I have put it in the page where I submit the data, or does it need to be on the data entry pages?

I the following error on the submit with that code...

Code: 
Microsoft VBScript compilation error '800a0414' 

Cannot use parentheses when calling a Sub 

/register/registercv.asp, line 31 

Replace(strSQL, "'", "")
------------------------^

Richard Noon
 
Upvote 0 Downvote
Can you post your code. I wouldn't use this method on your entire SQL string as it will remove ALL single quotes and you probably have some in there that you need - surrounding string variables for example.

Tony
[red]_________________________________________________________________[/red]
Webmaster -
 
Upvote 0 Downvote
for the example you need to escape the ' not remove it, as it is part of a name.

Code: 
strSQL = replace(strSQL,"'","''")

probably the second most asked question after "how do I hide my source code"

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
Nightclub counting systems

So long, and thanks for all the fish.
 
Upvote 0 Downvote
Hi, I can imagine that it is a common question...someone should write an FAQ!

This is the page that does the insert..it is the culmination of 3 pages of data entry prior to that.
below this...the error that I am still getting, now that I am using the line from Chris.

Code: 
<%@ LANGUAGE="VBSCRIPT" %>
<!--#include file="md5.asp" -->
<!--#include file="rand.asp" -->
<!--#include file="common.asp" -->


<html>

<%

	Password =  Request.Form("password")
	Sec_ques = Request.Form("sec_ques")
	Sec_Ans =  Request.Form("sec_Ans")
	first_name = Request.Form("first_name")
	last_name = Request.Form("last_name")
	Birth_Year = Request.Form("Birth_Year")
	Address = Request.form("Address")
	Post_Code = Request.Form("Post_Code")
	E_Mail = Request.Form("E_Mail")
	[URL unfurl="true"]www =[/URL]  Request.Form("[URL unfurl="true"]www")[/URL]
	Ethnicity = Request.form("Ethnicity")
	Nationality = Request.form("Nationality")
	Gender = Request.Form("Gender")
	County = Request.Form("County")
	Country = Request.Form("Country")
	PersonTelephoneNoH = Request.form("PersonTelephoneNoH")
	PersonTelephoneNoW = Request.form("PersonTelephoneNoW")
	FeeRate = Request.form("FeeRate")
	CareerDetail= Request.form("CareerDetail")

strSQL = replace(strSQL,"'","''")

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "D:/Websites/richard/admin/db/users.mdb"

	'Build connection
strSQL="insert into tblUsers (First_Name, Last_Name, Address, Birth_Year, Gender, E_Mail, Ethnicity, Nationality, www, Country, County, Post_Code, Sec_ques, Sec_Ans, [Password], PersonTelephoneNoW, PersonTelephoneNoH, CareerDetail, FeeRate) values ('" & First_Name& "', '" & Last_Name& "', '" & Address& "', '" & Birth_Year& "', '" & Gender& "', '" & E_Mail& "', '" & Ethnicity& "', '" & Nationality& "', '" & www& "', '" &Country & "', '" &County & "', '" & Post_Code& "', '" & Sec_ques& "', '" & Sec_Ans& "', '" & Password& "', '" & PersonTelephoneNoW& "', '" & PersonTelephoneNoH& "', '" & CareerDetail& "', '" & FeeRate& "')"
'response.write (strSQL)
		
	conn.Execute strSQL
conn.close

Response.Redirect("../upload2/temp/upload_demo.asp?E_Mail=" & E_Mail & "")
%>
<head>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Blue Resourcing - Registration Template</title>



</head>

<body>

<img border="0" src="picts/about.jpg" align="top" width="100%" height="80"></p>
<table border="0" width="100%" id="table2">
	<tr>
		<td>
		<p align="right">Help <a href="mailto:webenquiry@blueresourcing.com">
		Contact Us</a></td>
	</tr>
</table>
<form name "form">
<table border="0" width="100%" id="table1">
	<tr>
  		<td align="center" valign="top" width="156">
		<font color="#000080" face="Arial Narrow" size="2">
		<img border="0" src="../images/BR-Logo_125X110.gif" width="125" height="110" align="left"></font></td>
    <td width="161" height="23" align="right">
    <font face="Arial Narrow" size="2">Please Upload your CV:&nbsp;</font></td>
    <td width="804" height="23" bgcolor="#E9E9E9">
    <font color="#5C5CA8"><!--#include file="uploadtodatabase/insertfile.asp" --></font></td>
	</tr>
	</table>
</form>
<FORM NAME="form" METHOD="GET" ACTION="iiwuefh.htm" ONSUBMIT="return ValidateData();">
</body>
</html>
The error...
Line 41 is that line
Code: 
Microsoft JET Database Engine error '80040e14' 

Syntax error (missing operator) in query expression ''O'Leary', 'wrgelkete ewgseghseth szethgz er t', 'k023985428', 'Male', 'richardn@davidmasonconsultancy.co.uk', 'Asian British', 'Gambia', 'jhbwrbhj', 'Bahamas', 'rjnrkjgn', 'kjnekjn', 'What is your Pet Name?', 'rwgw', 'efw', 'jhrbgejrhb', 'wjrhbgejrhb'. 

/register/registercv.asp, line 41



Richard Noon
 
Upvote 0 Downvote
do it here instead:
Code: 
last_name = Replace(Request.Form("last_name"), "'", "''")

Tony
[red]_________________________________________________________________[/red]
Webmaster -
 
Upvote 0 Downvote
yes - the ones that contain strings anyway - not much point on the other ones (numeric, boolean, etc).

Tony
[red]_________________________________________________________________[/red]
Webmaster -
 
Upvote 0 Downvote
Before even considering using a 'built-up' SQL query, do a Google for 'SQL injection attack'. When you've read a few of the horror stories that result from it, look up 'stored procedures' or 'parameter queries' and find out how to do it properly

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
60
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
76
JScannell
JScannell
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
76
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
67
penguinspeaks
penguinspeaks
Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
179
Krus1972
Krus1972

Part and Inventory Search

Sponsor

Back
Top