MySql, jsp 1

[sensory21]

Hi all,

do you know how to discard this: '
ie if someone writes a company name in an input text box such as: John's House
I've got a mysql error because of the single quote, anyone knows? Im going to look on the manual at the same time.


Vero

 

[onpnt]

try out

private static String replaceTick(String text) {
return text.replace('\'', ' ');
}

and or regular expressions for a more usable function

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[sedj]

You don't need to discard that value - you can insert an apostrophe like this :

insert into test values ('John''s House');

 

[sensory21]

It's not working, the java function is ok it will replace the single quote by nothing but I want to keep the single quote and insert it into mysql database?

I need to keep the original layout of the name:
John's House
I used to use PHP and you could with a function insert the single quote in the database with a backslash.

Vero

 

[sedj]

Did you read my post ?!!!!

 

[sensory21]

No Sedj just now but I don't understand how do you insert John''s House

anyway John's House could be anything entered in a form by a company and the company name is contained in a variable so I'd like to have a solution in case of a single quote in the String variable company_name?

Vero

 

[onpnt]

derrr... forgot to add teh quote to that replace

apologies!!

sensory21.. we could go into a whole long discussion of why you should do this let alone the errors that are genearted by allowing ' in your SQL
reading up on some SQL Injection will greatly help you understand reasoning and security concerns better though

http://www.sitepoint.com/article/794

doubling the up in simple terms allows for the unterminted string error for one.
when it comes to SQL and if you do not double them up what happens is your statements are cut and ended mostly at the single instance of the apostrpophe.

so if you had
INERT INTO tbl SET names = 'john's'
all you get is
INERT INTO tbl SET names = 'john'

in all the second escapes the first
more explanations

http://www.4guysfromrolla.com/webtech/051899-1.shtml

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[sedj]

public class Test {


	public static void main(String args[]) throws Exception {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test");

		for (int i = 0; i < 3; i++) {
			aaa(conn, &quot;Joh's hhh&quot;);
			aaa(conn, &quot;Joh's Dad's ouse&quot;);
			aaa(conn, &quot;Jack's Mother's Da'dss's ''' hhh&quot;);

		}
		conn.close();



	}

	public static void aaa(Connection conn, String value) throws Exception {

		value = value.replaceAll(&quot;'&quot;, &quot;''&quot;);

		Statement s = conn.createStatement();
		s.executeUpdate(&quot;insert into test values ('&quot; +value +&quot;')&quot;);
		s.close();
	}
}

 

[venur]

Hi,

It is always a problem, when inserting a value with ' into the DB. Not only with insert but also with Update and Select the values with '. Java Provides with PreparedStatement with this you can avoid the above problem when inserting or updating the values in a table.

If you use a Statement instead of PreparedStatement every String value that you are inserting should pass through a function which replaces the '. Its all about writing a single function. But still a pain and not sure.

When MySQL Driver supports PreparedStatment's why don't we make best use of it.

String sql = "INSERT INTO my_table (col_string) VALUES(?)";
PreparedStatement pstmt = connection.prepareStatement(sql);

// Set the value
pstmt.setString(1, "john's"
// Insert the row
pstmt.executeUpdate();

It is just a suggestion.

Cheers,
Venu

 

[onpnt]

thanks Venu I was not a where of this built in function yet. It will absolutely benefit my development

* for the great tip

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[sensory21]

Yes I have read about this PreparedStatement but I am still not sure how to use it, will try!
Thanks
Vero

 

[sensory21]

If I may another question:

I am passing a variable from one file to the other like:

<a href=\&quot;updateprovider.jsp\&quot; id=\&quot;&quot; + providerId + &quot;\&quot;>

How do I collect the variable providerId in the updateprovider.jsp?

 

[sedj]

First your href is wrong - you need :

<a href=\&quot;updateprovider.jsp?\&quot; id=\&quot;&quot; + providerId + &quot;\&quot;>

in the JSP :

String val = request.getParameter(&quot;id&quot;

 

[sensory21]

Hi Sedj,

thank you but it doesn't work, in the updateprovider.jsp the string val equals to null?

And in the address of the a href, it says:
updateprovider.jsp?

that's all, shouldn't it be:
updateprovider.jsp? id = 34
but the source code is ok, says id=34

Shouldn't I have an hidden input type?

Cheers,
Vero

 

[sensory21]

Sorry for the trouble, have found out my syntax error:

<a href=\&quot;updateprovider.jsp?id=&quot; + providerId + &quot;\&quot;>

2 extra quotes!

Vero